Zoltán Németh wrote:
2007. 10. 3, szerda keltezéssel 05.21-kor jd.pillion@xxxxxxxxx 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
From the Manual
*
If there is no matching record for the right table in the |ON| or
|USING| part in a |LEFT JOIN|, a row with all columns set to
|NULL| is used for the right table. You can use this fact to find
records in a table that have no counterpart in another table:
mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;
This example finds all rows in |table1| with an |id| value that is
not present in |table2| (that is, all rows in |table1| with no
corresponding row in |table2|). This assumes that |table2.id| is
declared |NOT NULL|.
Here it goes:
select company.* from company left join contacts on company.companyId =
contacts.companyId where contacts.companyId IS NULL
Hope it helps...
Nadim Attari
Alienworkers.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
