Finding All That Match

[tgreen]

I need assistance building this MySQL query. Basically I am trying to find users that match all the id_num(s) in a query.
For example I want to find users (user_id) that have both 101 and 203 in the id_num field

Here is the sample table:

usr_id   id_num

USR1   101
USR2   101
USR3   101
USR1   201
USR2   203
USR3   204

Here is the query that does not work:

SELECT usr_id FROM table WHERE id_num = 101 AND id_num = 203 GROUP BY usr_id

I am trying to get back the following:

usr_id

USR2

[ultraslacker]

This is not how to query a database - it is impossible for your query to return any results, as it is impossible for id_num to equal 101 AND 203. Perhaps you should read up on SQL.

You may be able to do this with an aggregate together with HAVING -

SELECT usr_id, id_num
FROM test
WHERE id_num IN (101, 201)
GROUP BY usr_id
HAVING COUNT(usr_id) > 1;

Duplicate values for usr_id and id_num will throw off results, for instance given the following data

usr_id id_num
usr1 101
usr1 101
usr2 201

the query will result in

usr_id id_num
usr1 101