the opposite of a join?

------=_NextPart_000_0001_01C8057D.3CC407B0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I have a company table and a contacts table. In the contacts table, there
is a field called "companyID" which is a link to a row in the company table.



What is the easiest way to query the company table for all the company rows
whose ID is NOT linked to in the contact table? Basically, the opposite of a
join?



Thanks



J




------=_NextPart_000_0001_01C8057D.3CC407B0--

Re: the opposite of a join?

Actually you still want to use a join, just an OUTER join instead of an INNER
one.

With an OUTER join, you can get all the rows that match as well as rows where
it doesn't match:

http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join

In the example there, DepartmentID 36 is present in the `Employee` table but
not in the `Department` table so you get NULL for the `Department` data.

Then it's just a matter of checking for NULL. Remember that you can't do
"WHERE DepartmentID = NULL" because that will always end up being TRUE
(can't use regular comparisons with NULL), you have to use "WHERE
DepartmentID IS NULL".

-TG



----- Original Message -----
From:
To: ,
Date: Wed, 3 Oct 2007 05:21:06 -0500
Subject: [PHP] the opposite of a join?

> I have a company table and a contacts table. In the contacts table, there
> is a field called "companyID" which is a link to a row in the company table.
>
>
>
> What is the easiest way to query the company table for all the company rows
> whose ID is NOT linked to in the contact table? Basically, the opposite of a
> join?
>
>
>
> Thanks
>
>
>
> J
>
>
>
>
>

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

RE: the opposite of a join?

------=_NextPart_000_0011_01C805AA.F9EE43A0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

> Hi J,
>
>
> Checkout this,
>
>
> SELECT * FROM tbl_company where id not in (SELECT companyID from
> tbl_contacts)
>


Brilliant! This is exactly what I was looking for, and is quite
logical/readable! Thanks to everyone for the ideas!

J






>
>
>
>
> Regards,
> Lasitha Alawatta
> Application Developer
> Destinations of the World Holding Establishment
> P O Box: 19950
> Dubai, United Arab Emirates
> ( Ph +971 4 295 8510 (Board) / 1464 (Ext.)
> 7 Fax +971 4 295 8910
> + lasitha.a@dotw.com
>
> -----Original Message-----
> From: John Pillion [mailto:john@conv-dg.com]
On Behalf Of
> jd.pillion@gmail.com
> Sent: Wednesday, October 03, 2007 2:21 PM
> To: php-general@lists.php.net; php-db@lists.php.net
> Subject: [PHP-DB] the opposite of a join?
>
> I have a company table and a contacts table. In the contacts table,
> there
> is a field called "companyID" which is a link to a row in the company
> table.
>
>
>
> What is the easiest way to query the company table for all the company
> rows
> whose ID is NOT linked to in the contact table? Basically, the opposite
> of a
> join?
>
>
>
> Thanks
>
>
>
> J
>
>
>
> DOTW DISCLAIMER:
>
> This e-mail and any attachments are strictly confidential and intended
> for the addressee only. If you are not the named addressee you must not >
disclose, copy or take
> any action in reliance of this transmission and you should notify us as
> soon as possible. If you have received it in error, please contact the
> message sender immediately.
> This e-mail and any attachments are believed to be free from viruses but
> it is your responsibility to carry out all necessary virus checks and
> DOTW accepts no liability
> in connection therewith.
>
> This e-mail and all other electronic (including voice) communications
> from the sender's company are for informational purposes only. No such
> communication is intended
> by the sender to constitute either an electronic record or an electronic
> signature or to constitute any agreement by the sender to conduct a
> transaction by electronic means.
>

>


------=_NextPart_000_0011_01C805AA.F9EE43A0--