2

Basically, I have a dataset that is a list of internal location identifiers attached to an ID (so each 'set' of geographical points has its own ID). So an 'id' column and a 'location' column exist in the table.

I need a way to find an ID which contains two different locations, that is:

ID | location
--------------
1  | a
1  | b
1  | c
2  | a
2  | c
2  | d

If I looked for 'a' and 'd', I would get 2.
If I looked for 'a' and 'b', I would get 1.
If I looked for 'a' and 'c', I would get either 1 or 2.

I am using PostgreSQL 9.4.

6
select id
from the_table
where location in ('a', 'd')
group by id
having count(distinct location) = 2;
2

Shouldn't get it more simple than this:

SELECT ID FROM tbl GROUP BY ID HAVING '{a,c}' <@ array_agg(location)

Not the answer you're looking for? Browse other questions tagged or ask your own question.