MySQL in-operator must match all values?

Question

I've made my own forum. When doing a search I want to find any threads where two (or more) specific users have participated. I came up with this:

SELECT * FROM table1 INNER JOIN table2 
ON table1.threadid=table2.threadid 
WHERE table2.threadcontributor IN ('1','52512')

Before realizing that it actually means '1' OR '52512'.

Is there any way to make it work so that all id's has to match?

please post your real code or at least what are table1 and table2. my first aproach makes me join twice the thread table. please read this post and anwser http://stackoverflow.com/questions/7492699/how-can-i-structure-a-query-to-give-me-only-the-rows-that-match-all-values-in-a-c/7493309#7493309 — Saic Siquot, Sep 21 '11 at 18:58

score 29 · Accepted Answer · answered Sep 21 '11 at 18:57

29

SELECT * 
    FROM table1 
        INNER JOIN table2 
            ON table1.threadid=table2.threadid 
    WHERE table2.threadcontributor IN ('1','52512')
    GROUP BY table1.PrimaryKey
    HAVING COUNT(DISTINCT table2.threadcontributor) = 2

answered Sep 21 '11 at 18:57

Joe Stefanelli

132,803
19
237
235

1

two (or more) specific users so it should be `>=2` – Vishwanath Dalvi Sep 21 '11 at 18:59
4

@ViswanathanIyer But there are only 2 values specified in the `IN` clause. If the goal is to make sure you match all of the values listed in the `IN` clause, then the value tested in the `HAVING` should match the number of values specified for the `IN`. – Joe Stefanelli Sep 21 '11 at 19:01
@Joe - ViswanathanIyer isn't the OP. – Martin Smith Sep 21 '11 at 19:03
@JoeStefanelli MartinSmith : ) – Vishwanath Dalvi Sep 21 '11 at 19:05

MySQL in-operator must match all values?

1 Answers1

Linked