Geek asks and answers

LEFT OUTER JOIN query does not return expected rows

My goal is to do exactly what the LEFT OUTER JOIN intends to do using the 4th venn diagram : SQL diagrams :

enter image description here

My query does not return any values ​​whatsoever, where in fact it should return everything within Consulting_Memberships minus the one stored in Consultant_Memberships_Lists.

For easier understanding see 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

The query uses “5” as an identifier for demo purposes to try and select the correct rows.

+5
source share
2 answers

INNER JOIN - consultant_id = 5 WHERE. , :

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 Fiddle with Demo

+13

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;

Where, "consultant_memberships_list.consultant_id = 5", .

+2

More articles:


All Articles