0

Here is the sqlFiddle

I want to filter the users who have selected entities ,So if I want to filter user with entity say entity having ids "1" and "3" I hope to get the users which have both of these entities. No of entities selected can vary in number .

Query I am using is

 SELECT  user_id from user_entities where entity_id IN(1,3)

but for obvious reason it is returing me result as 

+----+-----------+---------+--------+
| ID | ENTITY_ID | USER_ID | STATUS |
+----+-----------+---------+--------+
|  1 |         1 |       3 |      1 |
|  2 |         3 |       3 |      1 |
|  7 |         1 |       2 |      1 |
| 29 |         3 |       1 |      1 |
+----+-----------+---------+--------+

So I will apply distinct to it it will give me user id with ids 1,2,3 but I only want user 3 as this is the only user having both entities .

What can be modified to get the exact results

alwaysLearn
  • 6,882
  • 7
  • 39
  • 67
  • 2
    GROUP BY user_id HAVING COUNT(*) = ? – Strawberry Aug 26 '14 at 08:02
  • Closely related to [SQL Query With & Without](http://stackoverflow.com/q/25080942/1446005) and [SQL statement - “join” vs “group by and having”](http://stackoverflow.com/q/477006/1446005). – RandomSeed Aug 26 '14 at 08:12

1 Answers1

0

You could join the table to itself specifying both IDs as part of the join condition:

SELECT e1.user_id
FROM user_entities e1
INNER JOIN user_entities e2
    ON e1.user_id = e2.user_id AND
       e1.entity_id = 1 AND
       e2.entity_id = 3;
cdhowie
  • 158,093
  • 24
  • 286
  • 300