Re: Re: [PHP] the opposite of a join?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx> 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
>
> >
> >
> > Thanks
> >
> >
> >
> > J
> >
> >
> >
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux