Find users that have ALL categories

From: Nick <nboutelier(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Find users that have ALL categories
Date: 2010-06-30 07:11:35
Message-ID: 4242fee6-0665-4d46-a30f-7853b0e1d89f@j17g2000prn.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Is this the most efficient way to write this query? Id like to get a
list of users that have the categories 1, 2, and 3?

SELECT user_id FROM user_categories WHERE category_id IN (1,2,3) GROUP
BY user_id HAVING COUNT(*) = 3

users_categories (user_id, category_id)
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
3 | 1
4 | 1
4 | 2
4 | 3

The result should produce 1 & 4.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message RP Khare 2010-06-30 07:37:23 Scheduling backup
Previous Message Adrian von Bidder 2010-06-30 06:09:34 Re: Migrating from MySQL