Apply WHERE clause to common elements in a row-CodePudding

I have a table like this:

     ID  pid  et  time  action 
 1   A    a    1    t1    -
 2   A    b    5    t2    r
 3   B    c    1    t3    -
 4   B    d    3    t4    -
 5   B    e    5    t5    r
 6   C    f    1    t6    -
 7   C    g    1    t7    -

I want to apply a filter in the where clause to return rows which meet certain criteria under a certain ID.

I want to return all the IDs that fall under these conditions

et = 3

action = 'r'

The result should be:

   ID pid et time action
1  A   a   1  t1    - 
2  A   b   5  t2    r
3  B   c   1  t3    -
4  B   d   3  t4    -
5  B   e   5  t5    r

Because ID = C is the only ID in the table above that doesn't have a C that follows the two conditions above

CodePudding user response：

You can use exists to check each Id matches

select * 
from t
where exists (
    select * from t t2 
    where t2.id=t.id and (t2.et=3 or t2.action='r')
)

See DB<>Fiddle

CodePudding user response：

If those are all the conditions where clause should be ...

where (action = 'r') OR (et= 3)

But this will return ONLY rows 2, 4, 5.

For your desired result you should use this:

select * from t where ID in (select ID from t where (action = 'r') OR (et=3))