MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE uu (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(30),
  PRIMARY KEY (`id`)
);
CREATE TABLE ww (
  id int NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE kk (
  id int NOT NULL AUTO_INCREMENT,
  uid int,
  wid int,
  state int,
  PRIMARY KEY (`id`),
  CONSTRAINT `kk_uid` FOREIGN KEY (uid) REFERENCES uu (id),
  CONSTRAINT `kk_wid` FOREIGN KEY (wid) REFERENCES ww (id)
);
CREATE TABLE aa (
  id int NOT NULL AUTO_INCREMENT,
  uid int,
  wid int,
  PRIMARY KEY (`id`),
  CONSTRAINT `aa_uid` FOREIGN KEY (uid) REFERENCES uu (id),
  CONSTRAINT `aa_wid` FOREIGN KEY (wid) REFERENCES ww (id)
);
insert into uu (name) values ('alice'), ('bob'), ('chuck');
insert into ww (id) values (10),(11);
insert into kk (uid,wid,state) values (2,10,99);
insert into kk (uid,wid,state) values (2,11,99);
insert into aa (uid,wid) values (2,10), (2,11);

Records: 3  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0


select u.*, "][", k.*, "][", a.*
     from uu u
    left join kk k on k.uid = u.id
    left join aa a on u.id = a.uid
    order by u.id,k.id;


id
name
][
id
uid
wid
state
][
id
uid
wid


1
alice
][
null
null
null
null
][
null
null
null

2
bob
][
1
2
10
99
][
2
2
11

2
bob
][
1
2
10
99
][
1
2
10

2
bob
][
2
2
11
99
][
2
2
11

2
bob
][
2
2
11
99
][
1
2
10

3
chuck
][
null
null
null
null
][
null
null
null


select a2.*, '][', k3.* -- distinct a2.wid
                                     from aa a2
                                     left join kk k3 on a2.uid = k3.uid and a2.wid = k3.wid
                                     where k3.wid is null
;


id
uid
wid
][
id
uid
wid
state


SELECT a.*
                     FROM aa a
                     where a.wid in
                           (
                                select distinct a2.wid
                                     from aa a2
                                     left join kk k3 on a2.uid = k3.uid and a2.wid = k3.wid
                                     where k3.wid is null
                           )
;


id
uid
wid


select u.*, '][k', k.*, '][k1', k1.*,'][tmp', tmp.*
     FROM uu u
    LEFT JOIN kk k ON k.uid = u.id
    LEFT JOIN kk k1 ON k1.uid = u.id AND k1.state != 99
    LEFT JOIN
           (
                SELECT a.*
                     FROM aa a
                     where a.wid in
                           (
                                select distinct a2.wid
                                     from aa a2
                                     left join kk k3 on a2.uid = k3.uid and a2.wid = k3.wid
                                     where k3.wid is null
                           )
           ) tmp ON tmp.uid = u.id
-- ;
   WHERE tmp.wid IS NULL
           AND (
                k.uid IS NULL
                OR
            (k.state = 99 AND k1.id IS NULL)
           )
;


id
name
][k
id
uid
wid
state
][k1
id
uid
wid
state
][tmp
id
uid
wid


1
alice
][k
null
null
null
null
][k1
null
null
null
null
][tmp
null
null
null

3
chuck
][k
null
null
null
null
][k1
null
null
null
null
][tmp
null
null
null


select u.*, '][k', k.*, '][k1', k1.*,'][tmp', tmp.*
     FROM uu u
    LEFT JOIN kk k ON k.uid = u.id
    LEFT JOIN kk k1 ON k1.uid = u.id AND k1.state != 99
    LEFT JOIN
           (
                SELECT a.*
                     FROM aa a
                     where a.wid in
                           (
                                select distinct a2.wid
                                     from aa a2
                                     left join kk k3 on a2.uid = k3.uid and a2.wid = k3.wid
                                     where k3.wid is null
                           )
           ) tmp ON tmp.uid = u.id
;


id
name
][k
id
uid
wid
state
][k1
id
uid
wid
state
][tmp
id
uid
wid


1
alice
][k
null
null
null
null
][k1
null
null
null
null
][tmp
null
null
null

2
bob
][k
2
2
11
99
][k1
null
null
null
null
][tmp
1
2
10

2
bob
][k
2
2
11
99
][k1
null
null
null
null
][tmp
2
2
11

2
bob
][k
1
2
10
99
][k1
null
null
null
null
][tmp
1
2
10

2
bob
][k
1
2
10
99
][k1
null
null
null
null
][tmp
2
2
11

3
chuck
][k
null
null
null
null
][k1
null
null
null
null
][tmp
null
null
null
id	name	][	id	uid	wid	state	][	id	uid	wid
1	alice	][	null	null	null	null	][	null	null	null
2	bob	][	1	2	10	99	][	2	2	11
2	bob	][	1	2	10	99	][	1	2	10
2	bob	][	2	2	11	99	][	2	2	11
2	bob	][	2	2	11	99	][	1	2	10
3	chuck	][	null	null	null	null	][	null	null	null