Finding combinations of specific values

Question

I don't know how to write the query for below.

My table is

I need to select distinct col1 id where both parameters exists in col2. eg. if i send 6,7 it should send me 5

please note this is not a recursive query. change the title accordingly. — Vamsi Prabhala, Dec 14 '16 at 13:37
Your question should provide the actual table definition (`CREATE TABLE` statement) and your Postgres version. — Erwin Brandstetter, Dec 14 '16 at 14:47

score 1 · Answer 1 · edited May 23 '17 at 11:45

This is probably among the fastest solutions:

SELECT col1  -- already DISTINCT?
FROM   tbl t1
JOIN   tbl t2 USING (col1)
WHERE  t1.col2 = 6
AND    t2.col2 = 7;

Assuming a PRIMARY KEY or UNIQUE constraint on (col1, col2), like it's typically implemented. Else add DISTINCT.

There are many other ways to implement relational division. Here are some:

score 0 · Accepted Answer · answered Dec 14 '16 at 13:37

0

Try:

SELECT col1
FROM mytable
WHERE col2 IN (6, 7)
GROUP BY col1
HAVING COUNT(DISTINCT col2) = 2

answered Dec 14 '16 at 13:37

Giorgos Betsos

(I hope the DISTINCT is redundant.) – Strawberry Dec 14 '16 at 13:53
No unique constraints are known, so the DISTINCT is needed. – jarlh Dec 14 '16 at 13:59

2 Answers2