Retrieve rows that matches with all the values listed

Question

Hi I need to get the rows which matches all the groupid listed as an array

SELECT user_id,group_id 
FROM group_privilege_details g
WHERE g.group_id in (102,101)

This will return me if any one of the groupid matches. But, I need userid which has all the groupid mention in the list.

score 2 · Accepted Answer · answered Oct 21 '11 at 13:12

2

Assuming that you cannot have duplicate user_id/group_id combinations:

SELECT user_id,count(group_id)
 FROM group_privilege_details g
WHERE g.group_id in (102,101) 
GROUP BY user_id
HAVING count(group_id) = 2

answered Oct 21 '11 at 13:12

Steven Mastandrea

2,752
20
26

Ok. Thanks. That is working. But, what if the list of group id will be in turn a subquery? That time I cannot user "having" – Developer404 Oct 21 '11 at 13:19
@Nila: Do you mean `WHERE g.group_id IN (SELECT ...)` instead of `WHERE g.group_id IN (102,101)`? If so, why do you think you wouldn't be able to use HAVING in that case? – Andriy M Oct 21 '11 at 13:43

score 1 · Answer 2 · edited May 23 '17 at 10:34

Here is a variant of Steven's query for generic arrays:

SELECT user_id
FROM   group_privilege_details
WHERE  group_id = ANY(my_array)
GROUP  BY 1
HAVING count(*) = array_length(my_array, 1)

Works as long as these requirements are met (not mentioned in the question):

(user_id, group_id) is unique in group_privilege_details.
array has only 1 dimension
base array-elements are unique

A generic solution that works regardless of these preconditions:

WITH   ids AS (SELECT DISTINCT unnest(my_array) group_id)
SELECT g.user_id
FROM   (SELECT user_id, group_id FROM group_privilege_details GROUP BY 1,2) g
JOIN   ids USING (group_id)
GROUP  BY 1
HAVING count(*) = (SELECT count(*) FROM ids)

unnest() produces one row per base-element. DISTINCT removes possible dupes. The subselect does the same for the table.

Extensive list of options for this kind of queries: How to filter SQL results in a has-many-through relation

Thanks. It gives me the idea for my query. – Developer404 Oct 22 '11 at 03:16 — Developer404, Oct 22 '11 at 03:16

score 0 · Answer 3 · answered Oct 22 '11 at 03:18

Please find my solved query:

select user_id,login_name from user_info where user_id in (
SELECT user_id  FROM 
group_privilege_details g WHERE g.group_id in 
(select group_id from group_privilege_details g,user_info u where u.user_id=g.user_id
and login_name='123')  
GROUP BY user_id HAVING count(group_id) = (select count(group_id) 
from group_privilege_details g,user_info u where u.user_id=g.user_id
and login_name='123') ) and login_name!='123'

Retrieve rows that matches with all the values listed

3 Answers3