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.
sonic1234Asked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
You can try using IN. For example, teGroupDataGUID IN ( 'Group3', 'Group2' ).
0
 
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
 
sonic1234Author Commented:
Thank you  - works perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.