If result returns null return everything

Question

I have the following dataset

ID    |   GROUP   |   ORGANIZATION   |     USERNAME
----------------------------------------------------
1          G1            ORG1               SKYLER
2          G1            ORG1               BRAD
3          G1            ORG1               CHAD
4          G2            ORG1               SKYLER
5          G3            ORG1               THAMIUS
6        (null)          ORG1               KYLE
7          G7            ORG2               TAYLOR
8          G7            ORG2               CLAY
9          G7            ORG2               WILL
10         G8            ORG2               KYLE

I then have a query which selects an organization and a username:

select group from table where organization = 'ORG1' and username = 'SKYLER'

It would return this:

 GROUP   
 -------
  G1   
  G2   

This is what I want returned for this query, but then I have a second scenario. If I go:

select group from table where organization = 'ORG1' and username = 'KYLE'

It returns null, but what I want is to return all the groups for 'ORG1':

 GROUP  
--------
  G1 
  G2    
  G3  

So basically if I select a user inside of an organization and they have a group assigned to them I want to return the groups. If they have no groups assigned to them, that means they are a kind of "super user" for the organization and it should return G1, G2, and G3 when Kyle is selected. I have tried using IFNULL function but ti doesnt allow for select statements inside of it. How can I achieve this?

Answer 1

You could use exists:

select distinct grp
from mytable
where organization = 'ORG1' and grp is not null and (
    username = 'SKYLER'
    or exists (
        select 1 
        from mytable 
        where organization = 'ORG1' and username = 'SKYLER' and grp is null
    )
)

You can also use window functions, if you are running MySQL 8.0:

select distinct grp
from (
    select t.*, max(username = 'KYLE' and grp is null) over() is_admin
    from mytable t
    where organization = 'ORG1' 
) t
where grp is not null and (username = 'KYLE' or is_admin = 1)

Demo on DB Fiddle

Results for Kyle:

grp
G1
G2
G3

Results for Skyler:

grp
G1
G2