You can also do an outer join and look for NULLS in the key (which means
"no match"). In some cases it may be more efficient. <>< Ryan

-----Original Message-----
From: James Ausmus [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 03, 2007 10:00 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; php-db@lists.php.net
Subject: [PHP-DB] Re: [PHP] RE: the opposite of a join?

On 10/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > 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
>

No, don't do this! It is a very inefficient way to retrieve the
information you are looking for (Use a query analysis tool to check it
out yourself, if you want) - if your tables get to any larger size at
all, it will start having a noticeable performance impact on your script
(not to mention your DB) - let the DB do the hard work and use a LEFT
JOIN syntax, the database can optimize that much more efficiently. Only
if your DB doesn't support the LEFT JOIN syntax would you want to do the
above.

-James



>
>
>
>
>
> >
> >
> >
> >
> > 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
> > + [EMAIL PROTECTED]
> >
> > -----Original Message-----
> > From: John Pillion [mailto:[EMAIL PROTECTED] 
> > <mailto:[EMAIL PROTECTED]>
> On Behalf Of
> > [EMAIL PROTECTED]
> > Sent: Wednesday, October 03, 2007 2:21 PM
> > To: [EMAIL PROTECTED]; 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.
> >
>
> >
>
>

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

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

Reply via email to