零以及无效的值

Question

我想通过比较 u.fld_id = ct.fld_teacher_id获取老师的信息如果ct.fld_teacher_id为null，我将显示未分配。但是用null CT.FLD_TEACHER_ID行列不显示在执行时。

这是我的查询。

  SELECT ct.fld_id as id, ct.fld_serial, ct.fld_city, ct.fld_venue_name, 
        ct.fld_start_time, ct.fld_end_time, 
        ct.fld_duration, ct.fld_available_seats, 
        ct.fld_course_price, ct.fld_status,
        c.fld_name, u.fld_first_name, u.fld_last_name,
        ct.fld_status, ct.fld_teacher_id, ct.fld_training_name,
        ct.fld_class, ct.fld_level, ct.fld_is_fixed, tt.fld_name as training_type_name
   FROM tbl_course_training as ct, tbl_users as u,
        tbl_courses as c, tbl_training_types as tt
   WHERE c.fld_id = ct.fld_course_id AND u.fld_id = ct.fld_teacher_id AND 
       ct.fld_teacher_id = 'NULL' AND tt.fld_id = ct.fld_training_type_id 

Answer 1

我在不知道结构的情况下无法整理您的查询。但是我注意到的事情很少。

• 您不能将null值与ct.fld_teacher_id = 'NULL'（ null）进行比较不是字符串）

  ct.fld_teacher_id is null -- Correct way
  

• 如果您需要选择ct.fld_teacher_id is null，则需要选择

  u.fld_id = isnull(ct.fld_teacher_id, u.fld_id) --or (sql-server) 
  u.fld_id = ct.fld_teacher_id or ct.fld_teacher_id is null --(my sql or sql-server)
  u.fld_id = COALESCE(ct.fld_teacher_id, u.fld_id)  --(my sql)
  

• 还避免使用旧方法From Table1 t1, Table2 t2

  From Table1 t1 JOIN Table2 t2 On t1.Key = t2.Key --Correct way
  

• 我认为您需要在这里有几个Left Join s。请检查一下链接了解SQL如何加入工作