PHP DB - Complex Left Join

Author

Complex Left Join

Keith Spiller

2005-10-31, 6:57 pm

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

Neil Smith [MVP, Digital media]

2005-10-31, 6:57 pm

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

Sponsored Links