I'm trying to pull payid with time = 0 if there is no other payid's on the same date for eeid.

The return should look like this:

I've tried doing different group bys and counts and just get stuck.

CodePudding user response：

You can use not exists to filter out rows for which another row exists with the same (eeid, date) and a non-0 time.

select t.*
from mytable t
where t.time = 0 and not exists (
    select 1
    from mytable t1
    where t1.eeid = t.eeid and t1.date = t.date and t1.time > 0
)

Demo on DB Fiddle

Technically we don't even need to filter on time in the where clause, this is sufficient :

select t.*
from mytable t
where not exists (
    select 1
    from mytable t1
    where t1.eeid = t.eeid and t1.date = t.date and t1.time > 0
)