Thanks Tom Doing as you suggested as well as applying the "st.group = 'B'" in the tbl4 ON achieved what I wanted.
Graham > -----Original Message----- > From: Tom Crimmins [mailto:[EMAIL PROTECTED] > Sent: 01 February 2005 00:03 > To: Graham Cossey > Cc: mysql@lists.mysql.com > Subject: RE: Help with a query using multiple LEFT JOINS > > > If you mean that you want to get a row even if tbl2 does not have > a matching > row for dcode, then move the conditions into the ON clause. > > Example based off of what you had: > > SELECT > FROM tbl1 as d > LEFT JOIN tbl2 as r ON (d.dcode=r.dcode AND r.mcode='AB' AND > r.year=2004 AND > r.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) > LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period <= 200412 AND > pc.to_period > 200412) > LEFT JOIN tbl4 as st ON st.scode=r.scode > > WHERE d.status!='X' > AND d.region='1A' > AND st.group = 'B' > > GROUP BY d.dcode, r.code > > You may want to do the same for tbl4 depending on the behavior you are > looking for. > > > --- > Tom Crimmins > Interface Specialist > Pottawattamie County, Iowa > > -----Original Message----- > From: Graham Cossey > Sent: Monday, January 31, 2005 5:48 PM > To: mysql@lists.mysql.com > Subject: Help with a query using multiple LEFT JOINS > > I'm hoping someone can help with a little problem I'm having with a query. > > In the query below I wish to return as least one row per tbl1, > however I am > only getting rows where there is at least an entry for tbl2 : > > SELECT ... > > FROM tbl1 as d > LEFT JOIN tbl2 as r ON d.dcode=r.dcode > LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period <= 200412 AND > pc.to_period > 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode > > WHERE r.mcode='AB' > AND d.status!='X' > AND d.region='1A' > AND r.year=2004 > AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12) > AND st.group = 'B' > > GROUP BY d.dcode, r.code > > > Can anyone help me see the light and show me where I'm being stupid? > > TIA > > Graham > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
