[thelist] MySQL / Left Join not returning NULL records
hmiller at cfpress.co.uk
Fri Jun 24 07:30:03 CDT 2011
On 24/06/2011 13:10, r937 wrote:
>> 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
> 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
> only rows with media='slider' and live=1
> so that's what filters out your unmatched rows -- if media is NULL and
> 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
> for all tables except the "driving" table, which is the one table from
> amongst your inner joined tables that has the most restrictive
> 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
> LEFT OUTER
> 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
> now has conditions only for the driving table
Clyde& Forth Press Ltd
Email: hmiller at cfpress.co.uk
This e-mail and any attachments are confidential and intended solely for the addressee. If you have received it in error, please inform the sender and delete it immediately. The views or opinions contained within this email may not be those of Clyde& Forth Press Ltd, which accepts no liability for any damage caused by the transmission of any viruses. E-mail traffic is monitored within Clyde& Forth Press Ltd and messages may be viewed.
Clyde& Forth Press Ltd is a company registered in Scotland (SC132609) with its registered office at Pitreavie Business Park, Dunfermline, Fife, KY11 8QS.
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
More information about the thelist