[sqlite] This query seems clumsy - can anyone do better?

[Kai Peters]

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