mysql - "mysql join with not equal to operator "不工作

Question

第一次尝试

SELECT s.name,s.email,s.student_id,a.hostel_id,s.image,s.course_id,s.year, s.address
FROM sys_student_account s, sys_room_allotment a
WHERE s.student_id!=a.student_id AND s.name like 'SAMS%'
GROUP BY s.student_id ORDER BY s.name LIMIT 0,20

第二次尝试

SELECT s.name,s.email,s.student_id,a.hostel_id,s.image,s.course_id,s.year, s.address
FROM sys_student_account s, sys_room_allotment a
WHERE NOT(s.student_id=a.student_id) AND s.name like 'SAMS%'
GROUP BY s.student_id ORDER BY s.name LIMIT 0,20

谁能帮帮我？

Answer 1

您需要正确理解联接。您的代码给出的结果与您想要的完全不同。

使用以下代码片段:

from sys_student_account s
left join sys_room_allotment a on ( s.student_id=a.student_id)

where a.student_id is NULL and s.name like 'SAMS%'

注意:检查是否需要group by子句并相应地实现。