[thelist] MySQL / Left Join not returning NULL records

Hugh Miller hmiller at cfpress.co.uk
Fri Jun 24 04:26:59 CDT 2011


List!

I have a query that I'm getting sick of looking at! In live application 
I will have a magazine website with a JQuery slider. In that slider I 
want to get content that has been set to publish into it, and have a 
fallback image to be used when no specific image exists.

There are three tables stories, sections and extras. The headline and 
URL contents will come from stories. The section name and the fallback 
image name will come from sections and the image to be used from extras. 
If no image is available in extras (and so no row will exist), the 
returned image name from sections will be used.

Here is the MySQL query (notes follow). I want it to return all rows 
from stories/sections even if there is no record in extras. Where have I 
made a pigs ear of it, I can't see? On running during testing it does 
return contents if hits are found in all tables. All tables are indexed.


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
LEFT JOIN extras AS ex ON st.story_id=ex.story_id

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'
AND st.indexorder > 0
AND st.live=1

AND sub.em > 0
AND sub.parent_id='5'
AND sub.live=1

AND ex.media='slider'
AND ex.live=1

ORDER BY st.indexorder DESC,st.published DESC, st.story_id DESC

NOTES
"st.site_id" numbers are syndication codes, the lowest number is the ID 
of the site, followed by region, country, UK, international
"st.section_id" is a main section, in this case 5 = Magazine
"st.indexorder" is the column that sets what content is to be found and 
used, CMS sets it as 10 first down to 0 unused
"sub.em" is the abbreviated name of the publication, value over 0 means 
it can be used on that publication
"sub.parent_id" is being requested to match to the st.section_id, ie. 
these subsections belong to that section
"ex.medianame" is a filename VARCHAR
"ex.media" is a description, in this case it is the file to be used on a 
JQuery slider
"published" is to prevent embargoed content from appearing
in all cases "live" is a flag marked to 0 when an item is deleted in the CMS
"subsection" column value 100 is a special code used for aggregated, non 
specific content - visible name is "Roundup"

-- 
Hugh Miller
Web Developer
Clyde&   Forth Press Ltd

Tel:   +44-01475-726511
Fax:   +44-01475-783734
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.



More information about the thelist mailing list