CODEWITHSUNDEEP
sqlpostgresql
user2721348
user2721348

Reputation: 435

postgres - finding unique users which have specific records

I need some help completing the following query with postgres.

I have the following table for example: (this is the essence of what im trying to accomplish)

lets call it - table_user_flags

user_id flag
1        1
1        2
2        1
2        3
3        1
3        2
3        3

I need to find all users which have records with specific flags (under flag column, at least 1 record with each flag)

Example: all users which have records with flags 1,3 (i.e 1 and 3) answer (user_id 2,3) (but also would like to make it simple to extend so that i can request flags 1,2,3,4,5 etc)

I tried many things, unfortunately I was not able to articulate the situation well enough to find an answer on google.

My best guess (which is lame and possibly faulty) is (and provided flags array - $flag_arr)

select a.user_id
from table_user_flags a, table_user_flags b
where a.flag = $flag_arr[0] and b.flag = $flag_arr[1] and a.user_id=b.user_id

but this strategy is slow, and also will only work if i need 2 flags, when i need more than 2 flags I will need to write specific queries for each length of the flags_arr.

How can I solve this?

I will appreciate any help!

Upvotes: 1

Views: 110

Answers (2)

roman
roman

Reputation: 117420

select user_id
from table_user_flags as t
    inner join (select unnest($flag_arr) as flag) as c on c.flag = t.flag 
group by t.user_id
having count(distinct t.flag) = array_length($flag_arr, 1);

or

select user_id
from table_user_flags as t
where t.flag = any($flag_arr)
group by t.user_id
having count(distinct t.flag) = array_length($flag_arr, 1);

sql fiddle demo

you can use count(*) if user_id, flag_id is unique inside the table

Upvotes: 0

Jakub Kania
Jakub Kania

Reputation: 16487

SELECT user_id
FROM table_user_flags
WHERE flag IN (1,2)
GROUP BY user_id
HAVING COUNT(DISTINCT flag)=2;

Fiddle

Upvotes: 2

Related Questions