Posted on 2011-10-30
Last Modified: 2012-08-13
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.
Question by:sonic1234
    LVL 59

    Expert Comment

    by:Kevin Cross
    You can try with NOT EXISTS.

    SELECT teStGUID, teStFname
    FROM tblStaff
       SELECT 1
       FROM tblStaffToGroups
       WHERE teGroupDataUser = teStGUID 
       AND  teGroupDataGUID = 'Group3'

    Open in new window


    Author Comment

    Thank you - that's perfect

    If I wanted to exclude more that one group - how could I do that?
    LVL 59

    Accepted Solution

    You can try using IN. For example, teGroupDataGUID IN ( 'Group3', 'Group2' ).

    Author Comment

    Thank you  - works perfectly.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now