This one is relatively simple: Drop the WHERE evtphoto.phtusage = 1. SELECT it and then GROUP ON it.
-- Loren McDonald <AKA MisterrMac> Gods Of Music Reviewer/Editor [EMAIL PROTECTED] http://www.GodsOfMusic.com > -----Original Message----- > From: Hoffman, Geoffrey [mailto:[EMAIL PROTECTED]] > Sent: Friday, January 24, 2003 7:28 PM > To: [EMAIL PROTECTED] > Subject: Need nulls in my join > > I have an SQL query that LEFT JOINs four different tables to a main table, > based on several foreign keys. > > It's returning only stories in a section that have photos, > but I need it to return all the stories in a section whether > it has a photo or not. > > I think the problem is in the > WHERE evtphoto.phtusage = 1 -- if there's no photo, > then there's no phtusage. > > Should I split this up into two queries, to get all > the story IDs for a section/day and then get the > photos for the stories in a 2nd query? > Or do I need a different join type? > Or do I put the WHERE clauses in a different order? > > Here's the query: > > > SELECT > evtstorysection.secid, > evtstory.styid, > evtstory.styheadline, > evtstory.stysummary, > evtstory.styintro, > evtstory.stypubdate, > evtphoto.phturl, > evtphoto.phtcaption, > evtstoryorder.stoorder, > evtlayoutsum.lytstring > > FROM > evtstory > > LEFT JOIN evtlayoutsum ON evtstory.slytid = evtlayoutsum.lytid > LEFT JOIN evtstorysection ON evtstory.styid = evtstorysection.styid > LEFT JOIN evtstoryorder ON evtstory.styid = evtstoryorder.styid > LEFT JOIN evtphoto ON evtstory.styid = evtphoto.styid > > WHERE > evtphoto.phtusage = 1 AND > evtstorysection.secid = 3 AND > evtstoryorder.secid = 3 AND > evtstoryorder.stodate = '2003-01-24' AND > evtstory.stypubdate <= '2003-01-24' AND > evtstory.styexpdate > '2003-01-24' > > ORDER BY > evtstoryorder.stoorder --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
