[thelist] MySQL / Left Join not returning NULL records

r937 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 --

   SELECT st.story_id
        , st.headline
        , st.published
        , sub.fullname
        , sub.url
        , sub.image
        , ex.medianame
     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 mailing list