• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

INNER JOIN and EXCLUSIONS

I have a database that has users in one table, groups in another, and users to groups one-to-many relationship in a third - thus;

-- tblStaff


teStGUID                  teStFname
-----------------------------
User1                        John
User2                        Mary
User3                        Fred
User4                        Jane



-- tblStaffGroups

teGroupGUID                  teGroupTitle
----------------------------------
Group1                        Red
Group2                        Green
Group3                        Blue


-- tblStaffToGroups

teGroupDataUser            teGroupDataGUID
-----------------------------------
User1                        Group1
User1                        Group2
User1                        Group3
User2                        Group1
User2                        Group2
User3                        Group2

How could I write a query to return all the records from tblStaff WHERE they are NOT members of Group3

So the result set would be

-- tblStaff


teStGUID                  teStFname
-----------------------------
User2                        Mary
User3                        Fred
User4                        Jane

Thus User1 (John) is excluded because he is a member of Group3, even though he is also a member of other groups.
User4 (Jane) is still included even though she is not a member of any groups.
0
sonic1234
Asked:
sonic1234
  • 2
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You can try with NOT EXISTS.

SELECT teStGUID, teStFname
FROM tblStaff
WHERE NOT EXISTS (
   SELECT 1
   FROM tblStaffToGroups
   WHERE teGroupDataUser = teStGUID 
   AND  teGroupDataGUID = 'Group3'
)
;

Open in new window

0
 
sonic1234Author Commented:
Thank you - that's perfect

If I wanted to exclude more that one group - how could I do that?
0
 
Kevin CrossChief Technology OfficerCommented:
You can try using IN. For example, teGroupDataGUID IN ( 'Group3', 'Group2' ).
0
 
sonic1234Author Commented:
Thank you  - works perfectly.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now