LEFT OUTER JOIN查询未返回预期的行 [英] LEFT OUTER JOIN query not returning expected rows

问题描述

我的目标是使用第四维恩图来完全完成左外连接的意图:

My aim is to do exactly what a LEFT OUTER JOIN intends to do using the 4th venn diagram: SQL Diagrams:

我的查询根本不返回任何值，实际上，它应该返回的是Consultant_Memberships中的所有值减去存储在Consultant_Memberships_Lists中的值.

My query isn't returning any values at all, where in fact, it should be returning all within the Consultant_Memberships minus the one that is stored within Consultant_Memberships_Lists.

请参阅 SQL小提琴以便更轻松地理解:

Please see the SQL Fiddle for an easier understanding:

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进行演示，以尝试选择正确的行.

The query is using '5' as an ID for demonstration purposes to try and pick out the correct rows.

推荐答案

由于WHERE子句中的consultant_id = 5，您当前的查询基本上在执行INNER JOIN.我相信您实际上是想使用:

You current query is basically doing an INNER JOIN because of the consultant_id = 5 on the WHERE clause. I believe you actually want to use:

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;

请参见带演示的SQL小提琴

这篇关于LEFT OUTER JOIN查询未返回预期的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！