On Mon, 28 Apr 2008 15:46:28 +0200, beatbox <downlode@gmail.com> wrote:

> On 28 Apr, 14:36, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> On Mon, 28 Apr 2008 15:30:50 +0200, beatbox <downl...@gmail.com> wrote:
>> > Hi all,
>> > I guess this must be a fairly common kind of query, but I'm not sure
>> > how to go about it.
>> > I have a table with columns nid and tid. I would like to return
>> > results for nid where there are entries for tid=X AND tid=Y. Sonid
>> > appears twice, and in each case tid matches a different specific
>> > value.
>>
>> > My query is as follows:
>>
>> > SELECT n.nid FROM node n, t WHERE t.nid=n.nid AND n.status=1
>> > AND t.tid= $group_term AND t.tid= $lesson_id ORDER BY n.nid DESC
>>
>> > Obviously this is not working, as it would only return a result if
>> > $group_term and $lesson_id were equivalent. They are not.
>>
>> SELECT DISTINCT n.nid
>> FROM term_node n
>> JOIN term_node j
>> ON j.nid = n.nid
>> AND j.tid = Y
>> WHERE n.tid = X
>
> Thanks for that, it gets me part way there... I still need to be able
> to check that status=1 for that nid in table "node". I will experiment
> with what you've posted.

SELECT DISTINCT n.nid
FROM node n
JOIN term_node t1
ON t1.nid = n.nid
AND t1.tid = X
JOIN term_node t2
ON t2.nid = n.nid
AND t2.tid = Y
WHERE n.status = 1
--
Rik Wasmus