On 28 Apr, 15:06, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Mon, 28 Apr 2008 15:46:28 +0200, beatbox <downl...@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. So nid
> >> > 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

Thanks! I was just looking into multiple JOIN statements.