I need to select from a table (user_id, i_id) only those values that match both user_ids.
Table structure:
user_id | i_id |
---|---|
713870562 | 2 |
713870562 | 3 |
713870562 | 4 |
713870562 | 5 |
713870562 | 6 |
713870562 | 7 |
713870562 | 8 |
713870562 | 9 |
22131245 | 6 |
22131245 | 7 |
22131245 | 8 |
22131245 | 9 |
22131245 | 10 |
22131245 | 11 |
22131245 | 12 |
22637245 | 32 |
I tried to do it with SELECT DISTINCT, but it selects all the data
SELECT DISTINCT interest_relations.user_id, interest_relations.i_id
FROM interest_relations
WHERE interest_relations.user_id IN (713870562,22131245) GROUP BY user_id, i_id
I expect to get only those values that are the same for both users
UPD.
There will be a lot of user IDs in the table. I need to filter only certain 2.
For example in the table above 3 id’s are presented. I want to filter only 713870562
and 22131245
and receive data like this:
i_id
6
7
8
9
2
Answers
Select all
user_id
which have a count greater than 1:see: DBFIDDLE
EDIT: After reading the answer from @stevanof-sm, I think I have mis-read the question. A simple query like next one may be all you need:
output:
Edited
An alternative with
inner join
and result for only 2user_id
-s. First take only data that is related to these two user ids and then use it as a CTE (pseudo-table).