-1

This is what a table looks like

Number   UniqID   status
555      1        in
555      1        in
555      1        in
555      2        in
555      2        out
555      2        in

I would like to select like this

Number   UniqID   status
555      1        in
555      1        in
555      1        in

and only select it like that if all the same uniqIDs have status in. if one of the status says out for the same ID skip the entire thing. Also the UniqID is automatically generated

and would like to display it like

Number    status
555       in
GMB
  • 216,147
  • 25
  • 84
  • 135
BornTo Live
  • 3
  • 2
  • UniqID isn't too unique, is it? But you want a "not exists" subselect, like this: https://stackoverflow.com/a/30923210/421195 – paulsm4 Nov 29 '20 at 23:19

1 Answers1

2

You can get the first resulset with not exists:

select *
from mytable t
where 
    t.status = 'in' 
    and not exists (
        select 1 
        from mytable t1 
        where t1.number = t.number and t1.uniqid = t.uniqid and t1.status = 'out'
    )

On the other hand, if you want all (number, uniqid) tuples for which all statuses are "in", aggregation is simpler:

select number, uniqid, min(status) as status
from mytable
group by number, uniqid
having min(status) = max(status) and min(status) = 'in'
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Hi there, thank you for a quick reply. but what if unique ID is not known, it is generated randomly and I still would like to grab the "number" where all the uniqID have status "in" and if one of them have "out" then skip the entire thing – BornTo Live Nov 29 '20 at 23:31
  • @BornToLive: the above queries do not hardcode anything about `unqid`, so they should do what you want (they would return the result you showed for your sample data). Note that it is rather counter-intuitive that something called `uniqid` is actually not unique in the table (as showed in your sample data). – GMB Nov 29 '20 at 23:37
  • got it , worked, thank you. Just have another question, how in final result get just one 555 instead of 3 of the same entry – BornTo Live Nov 29 '20 at 23:50
  • no worries, have figured it out, thank you for the help again – BornTo Live Nov 30 '20 at 00:06