I’m currently writing a query and can’t seem to get it right, so I’m writing to get some advice.
I have a props table [userId, name] (where name is a property).
| userId | name |
|---|---|
| 1 | A |
| 1 | B |
| 2 | B |
| 3 | A |
| 3 | B |
| 3 | C |
| 4 | A |
Suppose have data like this
To search for name, we received data of ["A","B"].
- To find the contents of any of them, I could have issued a query like the one below to find userId 1,2,3,4.
select userId from props where name in ("A","B") group by userId;
I’m not sure how to process the query to find something that has both.
If I want to find ["A","B"] as above, the search result should be two userId 1,3.
Thanks for reading my question.
>Solution :
If I want to find ["A","B"] as above, the search result should be two
userId 1,3.
You can do it as here in SQLFIddle
SELECT userId
FROM props
WHERE name IN ("A", "B")
GROUP BY userId
HAVING COUNT(DISTINCT name) = 2;
This will give you the expected result :
userId
1
3