How do I write this SQL query with 2 tables?

Question

There is a table called AssociateAuditorMap with 3 columns AssociateID, AuditorID and IsActive (bool).

Each AssociateID will have only one AuditorID mapped to it.One auditor may have many associates mapped to them.

There is a 2nd table called UserMaster with 2 columns UserID and RoleID.

Now given that Auditors are in the UserMaster with RoledID=2, what is the query to find auditors who do not have any associates mapped to them in the AssociateAuditorMap table?

That is, find Auditors who do not have any rows in the AssociateAuditorMap table.

Answer 1

How about

SELECT 
    u.UserID, u.RoleID
FROM 
    dbo.UserMaster u
WHERE
    u.RoleId = 2 
    AND NOT EXISTS (SELECT * FROM dbo.AssociateAuditorMap aam 
                    WHERE aam.AuditorID = u.UserID)

This would list all rows from UserMaster with a RoleID = 2 (auditors) that have no entry in the AssociateAuditorMap table with that AuditorID