[PHP-DB] 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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] 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-DB] Re: [PHP] the opposite of a join?
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 > >
[PHP-DB] 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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: [PHP] the opposite of a join?
Zoltán Németh 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 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
[PHP-DB] 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: <[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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] 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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
