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:james.ausmus@xxxxxxxxx] Sent: Wednesday, October 03, 2007 10:00 AM To: jd.pillion@xxxxxxxxx Cc: php-general@xxxxxxxxxxxxx; php-db@xxxxxxxxxxxxx Subject: Re: [PHP] RE: the opposite of a join? On 10/3/07, jd.pillion@xxxxxxxxx <jd.pillion@xxxxxxxxx> 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 > > + lasitha.a@xxxxxxxx > > > > -----Original Message----- > > From: John Pillion [mailto:john@xxxxxxxxxxx] > > <mailto:john@xxxxxxxxxxx%5D> > On Behalf Of > > jd.pillion@xxxxxxxxx > > Sent: Wednesday, October 03, 2007 2:21 PM > > To: php-general@xxxxxxxxxxxxx; php-db@xxxxxxxxxxxxx > > Subject: 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
