Search code examples
sqloracle-databasejoingroup-byhaving

Select Multiple Rows Based On Common Column Value Oracle SQL

I have a User table say like:

ID Name Ref_ID Active
1 ABC 100 1
2 BCD 200 0
3 CDE 300 1
4 DEF 300 0
5 EFG 300 1
6 FGH 400 0
7 GHI 400 0
8 HIJ 500 1
9 IJK 500 1

I want to find IDs and Names of those users who are all inactive under the same Ref_ID. What I mean to say is, say for example there are 3 users with Ref_ID = 300 and not all of them are inactive, so I won't want those users in the result. For users with Ref_ID = 400, all users inactive, hence I want them in the result set. I am trying to achieve a result similar to this:

ID Name
2 BCD
6 FGH
7 GHI

How can I achieve this? I have tried using group by and inner join queries but not able to get them right.

Solution

  • You can use not exists:

    select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.ref_id = t.ref_id and t2.active = 1
                 );

    Or window functions:

    select t.*
from (select t.*,
             max(active) over (partition by ref_id) as max_active
      from t
     ) t
where max_active = 0;