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]
