我的目标是使用第4个维恩图来完成LEFT OUTER JOIN打算做的事情:SQL Diagrams:
我的查询根本没有返回任何值,实际上,它应该返回Consultant_Memberships中的所有值减去Consultant_Memberships_Lists中存储的值.
请参阅SQL Fiddle以便更容易理解:
SELECT *
FROM consultant_memberships
LEFT OUTER JOIN consultant_memberships_list
ON consultant_memberships.`id` =
consultant_memberships_list.membership_id
WHERE consultant_memberships_list.consultant_id = $id
AND consultant_memberships_list.membership_id IS NULL
查询使用’5’作为ID用于演示目的,以尝试挑选正确的行.
最佳答案
由于WHERE子句上的consultant_id = 5,您当前的查询基本上是在进行INNER JOIN.我相信你真的想用:
SELECT *
FROM consultant_memberships m
LEFT OUTER JOIN consultant_memberships_list l
ON m.`id` = l.membership_id
AND l.consultant_id = 5
WHERE l.membership_id IS NULL;