you could do a RIGHT OUTER JOIN WHERE the company table is on the right to show you the companies that do not exist in the contacts table:
SELECT a.name, b.name FROM contacts a RIGHT OUTER JOIN company b ON a.company_id = b.id WHERE a.name IS NULL; results: +------+-----------+ | name | name | +------+-----------+ | NULL | Company C | +------+-----------+ contacts: +----+--------------+------------+ | id | name | company_id | +----+--------------+------------+ | 1 | Gerald Ford | 2 | | 2 | Jimmy Carter | 1 | | 3 | Bill Clinton | 2 | +----+--------------+------------+ company: +----+-----------+ | id | name | +----+-----------+ | 1 | Company A | | 2 | Company B | | 3 | Company C | +----+-----------+ -- matt On 10/3/07, Zoltán Németh <[EMAIL PROTECTED]> wrote: > > 2007. 10. 3, szerda keltezéssel 05.21-kor [EMAIL PROTECTED] ezt írta: > > 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? > > > > maybe something like > > SELECT * FROM company WHERE (SELECT COUNT(*) FROM contact WHERE > company_id = company.company_id)=0 > > it's not very efficient, but I don't have any better idea. someone else? > > greets > Zoltán Németh > > > > > > > Thanks > > > > > > > > J > > > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
