Postgresql - Select only duplicate values from the table

Humanoid
October 29, 2022
95 views
0 votes
2 Answers

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

Tags: postgresql sql

Answers

- Luuk
- October 29, 2022 at 3:55 pm
- 0 votes
0
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
Login or Signup to reply.

- Stefanovsm
- October 29, 2022 at 6:11 pm
- 0 votes
0
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
Login or Signup to reply.