Search Table for Multiple Results and Unique ID SQL

Question

I have a table with multiple rows of results, and I'm wanting to be able to search it for multiple values, but one unique ID

I'm not sure how to explain it, but say I have a user with multiple items assigned to them. So for example, I want to be able to search for a list of users that have item 1 and item 3 assigned to them, and return that users ID to get their info.

Example Table:

╔════╦══════╦══════╗
║ ID ║ USER ║ ITEM ║
╠════╬══════╬══════╣
║  1 ║  3   ║  1   ║
║  2 ║  5   ║  12  ║
║  3 ║  3   ║  3   ║
║  4 ║  1   ║  4   ║
╚════╩══════╩══════╝

Would return user 3 as having both item 1 and item 3

I'm not even sure where to start. Any ideas?? I can try add more info if needed, just not sure what.

see this question: http://stackoverflow.com/questions/11409869/mysql-nm-relationship-find-rows-with-several-specific-relations — Rufinus, Oct 31 '14 at 01:51
@Rufinus thanks for that. The first answer I had on here worked but has now been removed so can't vote for it. That makes sense! :) — dpDesignz, Oct 31 '14 at 01:57

score 2 · Accepted Answer · edited May 23 '17 at 10:33

2

Something like that should do the job:

SELECT *
FROM example
WHERE item IN (1,3)
GROUP BY user
HAVING COUNT(item) = 2

See Get all entries from Table B which have a relation to multiple entries (given list) from Table A for something similiar.

edited May 23 '17 at 10:33

Community

1
1

answered Oct 31 '14 at 01:56

wolfgangwalther

1,226
7
15

score 0 · Answer 2 · answered Oct 31 '14 at 01:57

You can use group by and having to do this. The general SQL is:

select user
from table t
group by user
having sum(case when item = 1 then 1 else 0 end) > 0 and
       sum(case when item = 3 then 1 else 0 end) > 0;

Each condition in the having clause counts the number of rows that match either 1 or 3. The > 0 specifies that each value needs to exist.

Search Table for Multiple Results and Unique ID SQL

2 Answers2