[thelist] MySQL / Left Join not returning NULL records
rudy at r937.com
Fri Jun 24 07:10:34 CDT 2011
> Where have I made a pigs ear of it, I can't see?
you've added specific WHERE conditions on the extras table
it works like this...
first, the FROM clause is executed, returning your stories and sections with
or without extras, and since that's a LEFT OUTER JOIN, if there are any rows
with no matching extras, then the columns in the result row which would've
come from the extras table are set to NULL
so far, this is exactly what a LEFT OUTER JOIN is supposed to do
but then, the WHERE clause is applied to the rows returned by the FROM
with your specific WHERE conditions on the extras table, you are saying that
regardless of whether the extras row was unmatched or not, you want to see
only rows with media='slider' and live=1
so that's what filters out your unmatched rows -- if media is NULL and live
is NULL, they cannot possibly be equal to those values
the solution is to move those conditions to the ON clause of the join
in fact, you could/should probably move all conditions into the ON clauses
for all tables except the "driving" table, which is the one table from
amongst your inner joined tables that has the most restrictive conditions,
in this case the stories table
like this --
FROM stories AS st
JOIN sections AS sub
ON sub.section_id = st.subsection
AND sub.em > 0
AND sub.parent_id = '5'
AND sub.live = 1
JOIN extras AS ex
ON ex.story_id = st.story_id
AND ex.media = 'slider'
AND ex.live = 1
WHERE st.site_id IN (27,1030,1297,1603,2000)
AND st.section_id = '5'
AND st.subsection <> 0
AND st.subsection <> 100
AND st.published <= '2011-06-24 10:09:37'
/* 9:37 !! coincidence? i don't think so !! */
AND st.indexorder > 0
AND st.live = 1
BY st.indexorder DESC
, st.published DESC
, st.story_id DESC
notice the ON clauses for the sections and extras tables -- the WHERE clause
now has conditions only for the driving table
More information about the thelist