My aim is to do exactly what a LEFT OUTER JOIN intends to do using the 4th venn diagram: SQL Diagrams:
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.
Use
The Where clause used before in your query "consultant_memberships_list.consultant_id = 5 " was neglecting the left outer join.
You current query is basically doing an
INNER JOIN
because of theconsultant_id = 5
on theWHERE
clause. I believe you actually want to use:See SQL Fiddle with Demo