11

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.

  • 1
    Currently your question has almost no information, only links to information elsewhere. Those links are fine, but your question should also contain the necessary information. – ruakh Feb 13 '13 at 19:58
  • 1
    (Also, your phrasing -- "in fact, [my query] should be [...]" -- makes it sound like you think you've written the query correctly, and the DBMS is simply running it wrong. I assure you, that's not the case.) – ruakh Feb 13 '13 at 19:59
  • (I agree with ruakh. I changed "correct" to "expected" in the title, and encourage the use of neutral/non-accusatory language.) –  Feb 13 '13 at 20:00
  • My apologies, I've added further information. –  Feb 13 '13 at 20:01

2 Answers2

20

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

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • @Bluefeet - It works perfectly and I've learnt more of the LEFT OUTER JOIN in the process. Thank you for your help, I appreciate it. –  Feb 13 '13 at 20:04
  • 2
    +1 That predicate in the WHERE clause is negating the "outer"-ness of the LEFT JOIN. The easiest way for me to think about the OUTER join is this: if a matching row is not found, then a dummy row is generated so that there is a matching row to return. That generated dummy row is going to consist entirely of NULL values. So any "IS NOT NULL" predicate in the WHERE clause is going to exclude all those dummy rows that were generated. – spencer7593 Feb 13 '13 at 20:07
3

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.

DevelopmentIsMyPassion
  • 3,541
  • 4
  • 34
  • 60
  • 1
    It might be more helpful if you mentioned what change you made to the query (i.e. moving a predicate from a WHERE clause to an ON clause), and a mention of why that change is necessary. – spencer7593 Feb 13 '13 at 20:10