CODEWITHSUNDEEP
postgresqlwhere-clause
rshar
rshar

Reputation: 1475

filter rows from Postgres table based on specific conditions without missing relevant rows

I have table with following columns in postgres. 

col1    col2             col3
1       Other            a
2       Drug             b    
1       Procedure        c
3       Combination Drug d
4       Biological       e
3       Behavioral       f
3       Drug             g
5       Drug             g
6       Procedure        h

I would like to filter rows based on following filters.

select col1, col2, col3
from tbl
where col2 in ('Other', 'Drug', 'Combination Drug', 'Biological')

But this query will exclude below rows

1       Procedure        c
3       Behavioral       f

The Desired output is:

col1    col2             col3
1       Other            a
1       Procedure        c
2       Drug             b    
3       Combination Drug d
3       Behavioral       f
3       Drug             g
4       Biological       e
5       Drug             g

How can I achieve the above output without missing the mentioned rows.

Any suggestion here will be really helpful. Thanks

Upvotes: 1

Views: 102

Answers (1)

forpas
forpas

Reputation: 164204

I think you want the rows where there is as col1 containing any of the values of col2 in the list: 

select col1, col2, col3
from tbl
where col1 in (
  select col1 from tbl 
  where col2 in ('Other', 'Drug', 'Combination Drug', 'Biological')
)
order by col1;

Or with EXISTS:

select t.col1, t.col2, t.col3
from tbl t
where exists (
  select 1 from tbl 
  where col1 = t.col1 
  and col2 in ('Other', 'Drug', 'Combination Drug', 'Biological')
)
order by col1;

See the demo.

Upvotes: 1

Related Questions