mysql - 使用连接获取第二个表中不存在的记录

Question

我需要从以下结果集中获取 pid 为 NULL 的记录:

pid    users_fee_schedule_students_name users_fee_schedule_students_uid

8       users                   6           MBA in IT   1   1337097600  3   6   user    250000
9       users                   6           MBA in IT   2   1337184000  3   7   user    250000
NULL    ashuser                 277         MBA in IT   1   1337097600  3   6   user    250000
NULL    ashuser                 277         MBA in IT   2   1337184000  3   7   user    250000
10      sriuser                 66          MBA in IT   1   1337097600  3   6   user    250000
NULL    sriuser                 66          MBA in IT 

我SQL查询得到上面的结果集是

SELECT FSP.pid,  users_fee_schedule_students.name AS users_fee_schedule_students_name, users_fee_schedule_students.uid AS users_fee_schedule_students_uid, fee_schedule.name AS fee_schedule_name, fee_schedule_instalments.instalment_no AS fee_schedule_instalments_instalment_no, fee_schedule_instalments.payable_by AS fee_schedule_instalments_payable_by, fee_schedule.fid AS fid, fee_schedule_instalments.iid AS fee_schedule_instalments_iid, 'user' AS field_data_field_school_course_user_entity_type, SUM(fee_schedule_instalments.amount) AS fee_schedule_instalments_amount
FROM 
ic_fee_schedule AS fee_schedule
LEFT JOIN ic_fee_schedule_students AS fee_schedule_students ON fee_schedule.fid = fee_schedule_students.fid
LEFT JOIN ic_users AS users_fee_schedule_students ON fee_schedule_students.uid = users_fee_schedule_students.uid
LEFT JOIN ic_fee_schedule_instalments AS fee_schedule_instalments ON fee_schedule.fid = fee_schedule_instalments.fid
LEFT JOIN ic_fee_schedule_payments AS FSP ON fee_schedule.fid = FSP.fid AND fee_schedule_students.uid = FSP.uid AND fee_schedule_instalments.iid = FSP.iid
WHERE (( (fee_schedule.fid = '3' ) ))
GROUP BY users_fee_schedule_students_name, users_fee_schedule_students_uid, fee_schedule_name, fee_schedule_instalments_instalment_no, fee_schedule_instalments_payable_by, fid , fee_schedule_instalments_iid

我觉得LEFT JOIN ic_fee_schedule_payments AS FSP 需要更改为其他类型的连接。正在检查中......

预先感谢您的帮助。

Answer 1

试过这个:

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

为了仅在表 A 中而不在表 B 中生成记录集，我们执行相同的左外连接，然后通过 where 子句从右侧排除我们不需要的记录。

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html