CODEWITHSUNDEEP
mysqlleft-joinsqlfiddle
user860511
user860511

Reputation:

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:

enter image description here

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.

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

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

Upvotes: 12

Views: 15240

Answers (2)

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

Use 

SELECT * 
   FROM   consultant_memberships 
       LEFT  Outer JOIN consultant_memberships_list 
                ON consultant_memberships_list.membership_id = consultant_memberships.`id`  
                and  consultant_memberships_list.consultant_id = 5 
 where  consultant_memberships_list.membership_id IS NULL;

The Where clause used before in your query "consultant_memberships_list.consultant_id = 5 " was neglecting the left outer join.

Upvotes: 3

Taryn
Taryn

Reputation: 247680

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;

See SQL Fiddle with Demo

Upvotes: 21

Related Questions