CODEWITHSUNDEEP
sqlpostgresql
Humanoid
Humanoid

Reputation: 43

Select only duplicate values from the table

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

Upvotes: 0

Views: 54

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13059

Edited
An alternative with inner join and result for only 2 user_id-s. First take only data that is related to these two user ids and then use it as a CTE (pseudo-table).

with t as (
 select * from interest_relations where user_id in (713870562, 22131245)
)
select i_id 
from t as a inner join t as b using (i_id)
where a.user_id < b.user_id;
i_id
6
7
8
9

Upvotes: 1

Luuk
Luuk

Reputation: 14978

Select all user_id which have a count greater than 1:

SELECT *
FROM interest_relations
WHERE user_id IN (SELECT user_id
                  FROM interest_relations
                  GROUP BY user_id
                  HAVING count(*)>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:

SELECT i_id, max(user_id) as "max", min(user_id) as "min"
FROM interest_relations
WHERE user_id in (22131245,715870562)
GROUP BY i_id;

output:

i_id max min
42 715870562 22131245
41 22131245 22131245
46 22131245 22131245
47 22131245 22131245
43 22131245 22131245
45 22131245 22131245
44 715870562 22131245

Upvotes: 0

Related Questions