Re: Re: the opposite of a join?
| From: | Matt Anderton | Date: | Wed, 03 Oct 2007 11:49:32 +0000 |
| Subject: | Re: Re: the opposite of a join? | ||
| References: | 1 2 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-44107@lists.php.net to get a copy of this message | ||
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 <znemeth@alterationx.hu> wrote:
>
> 2007. 10. 3, szerda keltezéssel 05.21-kor jd.pillion@gmail.com 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
>
>