Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

Next:From: RP KhareDate: 2010-06-30 07:37:23
Subject: Scheduling backup
Previous:From: Adrian von BidderDate: 2010-06-30 06:09:34
Subject: Re: Migrating from MySQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group