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

Question

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

edited the post. so row with id = 6 should be excluded from the desired table. — rshar
– rshar, Commented May 12, 2020 at 8:41

forpas · Accepted Answer · 2020-05-12 08:40:18Z

1

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.

answered May 12, 2020 at 8:40

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related