skip to Main Content

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


  1. 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.
  2. 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.
Please signup or login to give your own answer.
Back To Top
Search