[thelist] Simple SQL Problem

[Fred Jones]

> SELECT nid from nidtable
> LEFT JOIN linktable ON linktable.nid=nidtable.nid
> WHERE tid=5;
>
> This will give me 1 and 2.
>
> but how do I make a SELECT to get all rows from nidtable that have
> both a 5 and 6 in linktable? I want to get just row 1.

One way is to use multiple left joins:

SELECT nid from nidtable
LEFT JOIN linktable ON linktable.nid=nidtable.nid
LEFT JOIN linktable as Join2 ON Join2.nid=nidtable.nid
WHERE linktable.tid=5 and Join2.tid=6;

Seems to work. :)