Complex Left Join

------=_NextPart_000_0159_01C5DE1F.184D51C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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,=20
m.id, m.directorid, m.committee, m.position, m.year FROM directors as d, =
members as m=20
WHERE d.directorid =3D m.directorid=20
AND m.year =3D '2006'
ORDER BY d.lname, d.mname, d.fname;=20

Thank you for your help...


Keith
------=_NextPart_000_0159_01C5DE1F.184D51C0--

RE: Complex Left Join

try

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 d left outer join members m
ON
d.directorid = m.directorid
WHERE
AND m.year = '2006'
ORDER BY
d.lname, d.mname, d.fname;


your original query was an inner join

Bastien




>From: "Keith Spiller"
>To: "[PHP-DB]"
>Subject: [PHP-DB] Complex Left Join
>Date: Mon, 31 Oct 2005 13:29:18 -0700
>
>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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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"
>To: "[PHP-DB]"
>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