Hi,
the query below (ready to paste & run in the editor of your choice) works but I
have a strong
suspicion that it might be optimizable:
DROP TABLE IF EXISTS SecUserMembers;
DROP TABLE IF EXISTS SecUserGroups;
CREATE TABLE SecUserMembers(
UserGrpID integer NOT NULL,
UserID text NOT NULL,
Active integer NOT NULL,
A_Link integer NOT NULL,
B_Link integer NOT NULL
);
CREATE TABLE SecUserGroups(
UserGrpID integer NOT NULL PRIMARY KEY AUTOINCREMENT,
GroupName text NOT NULL
);
insert into SecUserGroups values (null, 'Alpha Group');
insert into SecUserGroups values (null, 'Beta Group');
insert into SecUserGroups values (null, 'Gamma Group');
insert into SecUserGroups values (null, 'Delta Group');
insert into SecUserMembers values (1, 'John Doe', 1, 0, 1);
insert into SecUserMembers values (2, 'John Doe', 1, 1, 0);
insert into SecUserMembers values (3, 'John Doe', 1, 1, 1);
insert into SecUserMembers values (1, 'Jane Doe', 1, 1, 0);
insert into SecUserMembers values (3, 'Jane Doe', 1, 0, 1);
select SUG.UserGrpID, SUG.GroupName, SUM.A_Link, SUM.B_Link
from SecUserGroups SUG
left outer join SecUserMembers SUM on SUM.UserGrpID = SUG.UserGrpID
where SUM.UserID = 'Jane Doe'
union
select SUG.UserGrpID, SUG.GroupName, 0, 0
from SecUserGroups SUG
where UserGrpID not in (select UserGrpID from SecUserMembers where
UserID = 'Jane Doe')
Thanks in advance for any pointers,
Kai
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
