Re: Complex Left Join

At 20:28 31/10/2005, php-db-digest-help@lists.php.net wrote:

Message-ID: <015d01c5de59$c5983770$6401a8c0@Evolution> From: "Keith Spiller" <larentium@hosthive.com> To: "[PHP-DB]" <php-db@lists.php.net> Date: Mon, 31 Oct 2005 13:29:18 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative;

        boundary="----=_NextPart_000_0159_01C5DE1F.184D51C0"

Subject: Complex Left Join Hi, Can anyone help me turn this into a left join? I want to get all of the records from the directors table whether matches exist in the members table or not. SELECT d.directorid, d.fname, d.mname, d.lname, d.title, d.suffix, d.active, d.function, m.id, m.directorid, m.committee, m.position, m.year FROM directors as d, members as m WHERE d.directorid = m.directorid AND m.year = '2006' ORDER BY d.lname, d.mname, d.fname; Thank you for your help...

Keith - it should read as : SELECT d.directorid, d.fname, d.mname, d.lname, d.title, d.suffix, d.active, d.function, m.id, m.directorid, m.committee, m.position, d.year FROM directors as d LEFT OUTER JOIN members as m ON d.directorid = m.directorid WHERE d.year = '2006' ORDER BY d.lname, d.mname, d.fname; BTW Anything with "no match", would have values of NULL for any fields of the m alias. You can't do m.year since if you have no match, there's no result to use for the WHERE so it would leave those rows out completely. You have to match on "d.year" where you're matching all results from d to m (including some with no matching record in the m table, but you still want the d. parts of the query). Does that make sense ? Cheers - Neil