Sql query to collect users having common items
I have a problem with Postgres. Here's an example: I have 3 tables: users, items and boxes
box:
user_id | item_id
1 | 3
1 | 4
1 | 6
1 | 7
2 | 5
2 | 10
2 | 11
3 | 5
3 | 6
3 | 7
Given this table, I would like to retrieve items among users that have a minimum of 2. So the expected SQL query result should be item_id: 6, 7 because user 1 and user 3 share items 6 and 7. But user 2 and 3 only one element is used: element 5, so element 5 is not the result.
I try so many ways without success. I wonder if anyone can help me.
+3
source to share
1 answer
Try it. It returns 6 and 7 (and 5,6,7 if you add the entry "1.5"), but I haven't tested it extensively.
-- The Outer query gets all the item_ids matching the user_ids returned from the subquery
SELECT DISTINCT c.item_id FROM boxes c -- need DISTINCT because we get 1,3 and 3,1...
INNER JOIN boxes d ON c.item_id = d.item_id
INNER JOIN
--- the subquery gets all the combinations of user ids which have more than one shared item_id
(SELECT a.user_id as first_user,b.user_id as second_user FROM
boxes a
INNER JOIN boxes b ON a.item_id = b.item_id AND a.user_id <> b.user_id -- don't count items where the user_id is the same! Could just make the having clause be > 2 but this way is clearer
GROUP BY a.user_id,b.user_id
HAVING count(*) > 1) s
ON s.first_user = c.user_id AND s.second_user = d.user_id
+2
source to share