CODEWITHSUNDEEP
sqlpostgresql
Tomer Shalhon
Tomer Shalhon

Reputation: 225

How to select value that matches all values in a list?

I am trying to get two separate row values, for 2 conditions on the same column.

for example with this data:

id       status
----------------
1        0
1        2
1        3
2        2
2        0

I want to select all the rows where the status = 0, and status = 2.

So for example the output should be:

id       status
----------------
1        0
1        2
2        0
2        2

Thank you!

Upvotes: 0

Views: 1383

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

One method is:

where status in (0, 2)

But I suspect you want both values for the id. In that case, one method uses exists:

select t.*
from t
where status in (0, 2) and
      exists (select 1
              from t t2
              where t2.id = t.id and
                    t2.status in (0, 2) and
                    t2.status <> t.status
             );

If you just want the ids, then aggregation is easy:

select id
from t
where status in (0, 2)
group by id
having count(*) = 2;

This can be incorporated in a query to get the original rows using in, exists, or join. Or window functions:

select t.*
from (select t.*,
             count(*) filter (where status in (0, 2)) over (partition by id) as cnt
      from t
     ) t
where cnt = 2;

Upvotes: 2

forpas
forpas

Reputation: 164139

To get the ids that you want you need to group by id and count the distinct values of status:

select * from tablename
where 
  status in (0, 2)
  and
  id in (
    select id from tablename
    where status in (0, 2)
    group by id
    having count(distinct status) = 2
  )

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

Using HAVING COUNT(DISTINCT status) = 2, you can filter and get the id, and based on the ids using sub query with WHERE clause you can achieve the expected output:

SELECT * 
FROM TableName 
WHERE ID IN (
    SELECT id
    FROM TableName
    WHERE status IN (0, 2)  
    GROUP BY id
    HAVING COUNT(DISTINCT status) = 2 
) AND status IN (0, 2)

Working demo on db<>fiddle

Upvotes: 1

Related Questions