By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
ID INTEGER NOT NULL
,grp VARCHAR(6)
,ORGANIZATION VARCHAR(4) NOT NULL
,USERNAME VARCHAR(7) NOT NULL
);
INSERT INTO mytable(ID,grp,ORGANIZATION,USERNAME) VALUES (1,'G1','ORG1','SKYLER');
INSERT INTO mytable(ID,grp,ORGANIZATION,USERNAME) VALUES (2,'G1','ORG1','BRAD');
INSERT INTO mytable(ID,grp,ORGANIZATION,USERNAME) VALUES (3,'G1','ORG1','CHAD');
INSERT INTO mytable(ID,grp,ORGANIZATION,USERNAME) VALUES (4,'G2','ORG1','SKYLER');
INSERT INTO mytable(ID,grp,ORGANIZATION,USERNAME) VALUES (5,'G3','ORG1','THAMIUS');
INSERT INTO mytable(ID,grp,ORGANIZATION,USERNAME) VALUES (6,null,'ORG1','KYLE');
INSERT INTO mytable(ID,grp,ORGANIZATION,USERNAME) VALUES (7,'G7','ORG2','TAYLOR');
INSERT INTO mytable(ID,grp,ORGANIZATION,USERNAME) VALUES (8,'G7','ORG2','CLAY');
INSERT INTO mytable(ID,grp,ORGANIZATION,USERNAME) VALUES (9,'G7','ORG2','WILL');
INSERT INTO mytable(ID,grp,ORGANIZATION,USERNAME) VALUES (10,'G8','ORG2','KYLE');
select distinct grp
from mytable
where organization = 'ORG1' and grp is not null and (
username = 'KYLE'
or exists (
select 1
from mytable
where organization = 'ORG1' and username = 'KYLE' and grp is null
)
)
| grp |
|---|
| G1 |
| G2 |
| G3 |
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)
| grp |
|---|
| G1 |
| G2 |
| G3 |