Databases
StackOverflow2013
Postsdbo
POQuery for Users not in a specific group? (Wanted to use EXCEPT but it seems MySQL doesn't support it)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuery for Users not in a specific group? (Wanted to use EXCEPT but it seems MySQL doesn't support it)
    text
    copied!<p>I have a many to many relationship between users and groups:</p> <pre><code>CREATE TABLE IF NOT EXISTS `SecurityIdentifiers` ( `Guid` char(36) NOT NULL, PRIMARY KEY (`Guid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `CaseIdUsers` ( `Sid` char(36) NOT NULL, `Acl` int(11) NOT NULL, `FirstName` varchar(45) NOT NULL, `LastName` varchar(45) NOT NULL, `CaseID` varchar(8) NOT NULL, PRIMARY KEY (`Sid`), UNIQUE KEY `CaseID_UNIQUE` (`CaseID`), KEY `fk_CaseIDUsers_AccessControlLists1` (`Acl`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `GroupMembers` ( `User` char(36) NOT NULL, `Group` char(36) NOT NULL, PRIMARY KEY (`User`,`Group`), KEY `fk_Groups_has_SecurityIdentifiers_SecurityIdentifiers1` (`User`), KEY `fk_Groups_has_SecurityIdentifiers_Groups1` (`Group`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `Groups` ( `Sid` char(36) NOT NULL, `Acl` int(11) NOT NULL, `Name` varchar(45) NOT NULL, `Description` varchar(255) NOT NULL, PRIMARY KEY (`Sid`), UNIQUE KEY `Name_UNIQUE` (`Name`), KEY `fk_Groups_Access Control Lists1` (`Acl`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; </code></pre> <p>I need to find all the users who aren't in a specific group. I wanted to do something like:</p> <pre><code>SELECT CaseId FROM CaseIdUsers EXCEPT SELECT CaseId FROM CaseIdUsers JOIN GroupMembers ON GroupMembers.User = CaseIdUsers.Sid JOIN Groups ON GroupMembers.Group = Groups.Sid WHERE Groups.Name = 'MyGroupName' </code></pre> <p>But MySQL doesn't support <code>EXCEPT</code>. What do I do now?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload