CODEWITHSUNDEEP
sqlpostgresql
Kron
Kron

Reputation: 483

SQL query to get rows where ID is the same based on remaining contents

for the purpose of this example let's say I have a fairly simple table:

-----------
| ID | Val|
-----------
| 01 | XX |
| 01 | YY |
| 03 | AA |
| 04 | XX |
| 02 | BB |
| 02 | AA |
| 03 | XX |
| 02 | XX |
-----------

And what I want to select is the IDs where Val has XX and AA so basically my result would be 03 and 02 because they are the only IDs with Vals that have both of those values. Even though 02 also has BB and even though 01 an 04 also have XX.

I am just a bit stumped as to how to write this query.

Upvotes: 0

Views: 51

Answers (4)

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

You want a result set that is an intersection of two sets:

select ID
from yourTable
where Val in ("XX")
intersect
select ID
from yourTable
where Val in ("AA")

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Here is one method for Postgres:

select id
from t
where val = any(array['XX', 'AA'])
group by id
having count(*) = cardinality(array['XX', 'AA']);

Note that this allows you to pass in the values of interest as an array. Only one array parameter is needed, regardless of the number of values.

If duplicates are allowed, you will need count(distinct val) instead of count(*).

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You need to try this:

SELECT ID
FROM yourTable
WHERE Val IN ('XX', 'AA')
GROUP BY ID
HAVING
    COUNT(DISTINCT VAL) = 2;

Cheers!!

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

Using aggregation:

SELECT ID
FROM yourTable
WHERE Val IN ('XX', 'AA')
GROUP BY ID
HAVING MIN(Val) <> MAX(Val);

The idea here is to aggregate by ID, restricting to only the XX and AA values, then assert that there are in fact two different values. If the assertion passes, then it implies that the ID has both values.

Note that the above query can take advantage of an index on (ID, Val), because of the way the HAVING clause is written.

Upvotes: 2

Related Questions