[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
Re: [PHP] the opposite of a join?
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 General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] 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: [EMAIL PROTECTED] To: php-general@lists.php.net, [EMAIL PROTECTED] 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: [PHP] the opposite of a join?
- Original Message - From: Zoltán Németh [EMAIL PROTECTED] it's not very efficient, but I don't have any better idea. someone else? Indeed, that sort of query is one of the worst and there is little you can do to improve it save making sure you have an index on the field of the table pointed at, even if you create it for this query and drop it once done. greets Zoltán Németh Thanks J -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.488 / Virus Database: 269.13.39/1045 - Release Date: 02/10/2007 18:43 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] the opposite of a join?
On 10/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: 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? SELECT company.* FROM company LEFT JOIN contacts ON (company.companyID = contacts.companyID) WHERE contacts.companyID IS NULL (Assuming your DB can handle a left join) -James Thanks J
Re: [PHP] the opposite of a join?
[EMAIL PROTECTED] wrote: 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? SELECT * FROM company WHERE id NOT IN (SELECT companyID FROM contacts); -- 21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
