CODEWITHSUNDEEP
sqlms-access
Billy
Billy

Reputation: 212

SQL List all of MemberID's of people who arent in the ones listed

Outputted Date Design View

I have a seperate Members table which has all the members ID's and I want to list all those but get rid of the ones that are displayed in this list.

SELECT DISTINCT tbl_classregistration.ClassID, tbl_classregistration.MemberID
FROM tbl_member INNER JOIN (tbl_classes 
INNER JOIN tbl_classregistration ON 
           tbl_classes.ClassID = tbl_classregistration.ClassID) ON              
           tbl_member.MemberID = tbl_classregistration.MemberID
GROUP BY tbl_classregistration.ClassID, tbl_classregistration.MemberID
HAVING (((tbl_classregistration.ClassID)=[Enter ClassID]));

Thats the SQL View

Upvotes: 0

Views: 29

Answers (1)

Daniel Marcus
Daniel Marcus

Reputation: 2686

Use not in:

select memberid from members where memberid not in (SELECT DISTINCT tbl_classregistration.MemberID
FROM tbl_member INNER JOIN (tbl_classes INNER JOIN tbl_classregistration ON tbl_classes.ClassID = tbl_classregistration.ClassID) ON tbl_member.MemberID = tbl_classregistration.MemberID
GROUP BY tbl_classregistration.ClassID, tbl_classregistration.MemberID
HAVING (((tbl_classregistration.ClassID)=[Enter ClassID])))

Upvotes: 1

Related Questions