[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