[thelist] Simple SQL Problem

[r937]

they'd be INNER JOINs, not LEFT OUTER JOINs

multiple joins work, but clearly become unweildy the more conditions you
need, as well as completely unmanageable in cases where you want, for
example, "at least three of five"

;o)

anyhow, here's a simpler solution:

   SELECT nidtable.nid
        , nidtable.summat -- other column(s) required
     FROM nidtable
   INNER
     JOIN ( SELECT nid
              FROM linktable
             WHERE tid IN ( 5 , 6 )
            GROUP
                BY nid
            HAVING COUNT(*) = 2
          ) AS ok_nids
       ON ok_nids.nid = nidtable.nid

all the magic occurs in the derived table (the subquery in the FROM clause)

the count in the example above makes sure that 2 tids exist for each nid in
order for the nid to be deemed acceptable

in the "at least three of five" example, there would be 5 tids in the IN
list, but the HAVING clause would have COUNT(*) >= 3

anyhow, the nids which meet this criterion are then joined back to the
nidtable, so that other columns from the desired nids can be retrieved

simple, eh?

;o)

___________________________
rudy
http://r937.com/
buy my book "Simply SQL" from Amazon --
http://www.amazon.com/exec/obidos/ASIN/0980455251/