RE: Need nulls in my join
Seeing a little more data on your situation does change things quite a bit. It sounds like you are also needing to use a SELECT DISTINCT of specific criteria, so that you avoid pulling the same thing more than once. What those criteria are, naturally depends on what the various possibilities are and how (exactly) you want to pull the data. -- Loren McDonald [EMAIL PROTECTED] > -Original Message- > From: Hoffman, Geoffrey [mailto:[EMAIL PROTECTED]] > Sent: Saturday, January 25, 2003 3:48 PM > To: [EMAIL PROTECTED] > Subject: RE: Need nulls in my join > > Unfortunately I tried to implement what you suggested, but in my case it > didn't work. > > I have stories that can have zero or more photos, one or more author, one > or > more sections, and one or more active dates. > > Trying to join all those items together using your suggestion actually did > return stories with no photo, but I would then get 3 copies of a story > that > had 3 photos, and 6 copies of a 2-photo story that appears on 3 days. > > What I really need is mysql 4's SELECT blah WHERE (SELECT blah) subselect > feature, but that's not an option either due to the host I'm using. I > ended > up writing a simpler query get all the unique story id's for a section, > then > doing a foreach loop in PHP to query for all the additional data for the > story where ID = $loopindex. > > I'm sure there's a supermysqlguru out there who cringes when you issue a > bunch of queries in a PHP loop, but it works fine for now. > > Thanks though - I'm sure I'll use your suggestion somewhere in the app I'm > working on. > > Geoff > > > -Original Message- > > From: Loren McDonald [mailto:[EMAIL PROTECTED]] > > Sent: Saturday, January 25, 2003 1:41 PM > > To: Hoffman, Geoffrey; [EMAIL PROTECTED] > > Subject: RE: Need nulls in my join > > > > > > Oopssorry. That should have been GROUP BY and not GROUP ON > > > > That's what I get for answering a post at 2am. :) > > > > -- > > Loren McDonald > > [EMAIL PROTECTED] > > > > > > > This one is relatively simple: > > > > > > Drop the WHERE evtphoto.phtusage = 1. > > > SELECT it and then GROUP ON it. > > > > > > -- > > > Loren McDonald - 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
Re: Need nulls in my join
Hi. On Sat 2003-01-25 at 14:25:14 -0400, [EMAIL PROTECTED] wrote: > On Fri, 2003-01-24 at 20:27, Hoffman, Geoffrey wrote: [...] > > 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. [...] You simply have to move that condition from the WHERE clause to the appropriate ON clause (and drop the part you put in to handle the case without photo): SELECT evtstorysection.secid, [...] FROM evtstory LEFT JOIN evtlayoutsum ON evtstory.slytid = evtlayoutsum.lytid LEFT JOIN evtstorysection ON evtstory.styid = evtstorysection.styid LEFT JOIN evtstoryorderON evtstory.styid = evtstoryorder.styid LEFT JOIN evtphoto ON evtstory.styid = evtphoto.styid AND evtphoto.phtusage = 1 WHERE 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 HTH, Benjamin. -- [EMAIL PROTECTED] - 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
RE: Need nulls in my join
Unfortunately I tried to implement what you suggested, but in my case it didn't work. I have stories that can have zero or more photos, one or more author, one or more sections, and one or more active dates. Trying to join all those items together using your suggestion actually did return stories with no photo, but I would then get 3 copies of a story that had 3 photos, and 6 copies of a 2-photo story that appears on 3 days. What I really need is mysql 4's SELECT blah WHERE (SELECT blah) subselect feature, but that's not an option either due to the host I'm using. I ended up writing a simpler query get all the unique story id's for a section, then doing a foreach loop in PHP to query for all the additional data for the story where ID = $loopindex. I'm sure there's a supermysqlguru out there who cringes when you issue a bunch of queries in a PHP loop, but it works fine for now. Thanks though - I'm sure I'll use your suggestion somewhere in the app I'm working on. Geoff > -Original Message- > From: Loren McDonald [mailto:[EMAIL PROTECTED]] > Sent: Saturday, January 25, 2003 1:41 PM > To: Hoffman, Geoffrey; [EMAIL PROTECTED] > Subject: RE: Need nulls in my join > > > Oopssorry. That should have been GROUP BY and not GROUP ON > > That's what I get for answering a post at 2am. :) > > -- > Loren McDonald > [EMAIL PROTECTED] > > > > This one is relatively simple: > > > > Drop the WHERE evtphoto.phtusage = 1. > > SELECT it and then GROUP ON it. > > > > -- > > Loren McDonald > > > > - 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
RE: Need nulls in my join
Oopssorry. That should have been GROUP BY and not GROUP ON That's what I get for answering a post at 2am. :) -- Loren McDonald [EMAIL PROTECTED] > This one is relatively simple: > > Drop the WHERE evtphoto.phtusage = 1. > SELECT it and then GROUP ON it. > > -- > Loren McDonald - 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
Re: Need nulls in my join
On Fri, 2003-01-24 at 20:27, Hoffman, Geoffrey wrote: > 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 evtlayoutsumON evtstory.slytid = evtlayoutsum.lytid > LEFT JOIN evtstorysection ON evtstory.styid = evtstorysection.styid > LEFT JOIN evtstoryorder ON evtstory.styid = evtstoryorder.styid > LEFT JOIN evtphotoON evtstory.styid = evtphoto.styid LEFT JOIN evtphoto nophoto ON evtstory.styid = nophoto.styid > > WHERE > evtphoto.phtusage = 1 AND Replace above line with: (evtphoto.phtusage = 1 OR nophoto.phtusage IS NULL) 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 > __ / \\ @ __ __@ Adolfo Bello <[EMAIL PROTECTED]> / // // /\ / \\ // \ // Bello Ingenieria S.A, ICQ: 65910258 / \\ // / \\ / // // / //cel: +58 416 609-6213 /___// // / <_/ \__\\ //__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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
RE: Need nulls in my join
This one is relatively simple: Drop the WHERE evtphoto.phtusage = 1. SELECT it and then GROUP ON it. -- Loren McDonald 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 evtlayoutsumON evtstory.slytid = evtlayoutsum.lytid > LEFT JOIN evtstorysection ON evtstory.styid = evtstorysection.styid > LEFT JOIN evtstoryorder ON evtstory.styid = evtstoryorder.styid > LEFT JOIN evtphotoON 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
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 evtlayoutsumON evtstory.slytid = evtlayoutsum.lytid LEFT JOIN evtstorysection ON evtstory.styid = evtstorysection.styid LEFT JOIN evtstoryorder ON evtstory.styid = evtstoryorder.styid LEFT JOIN evtphotoON 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
