At 20:28 31/10/2005, php-db-digest-help@xxxxxxxxxxxxx wrote:
Message-ID: <015d01c5de59$c5983770$6401a8c0@Evolution>
From: "Keith Spiller" <larentium@xxxxxxxxxxxx>
To: "" <php-db@xxxxxxxxxxxxx>
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
