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 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
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
