Hi there, I'm going to get some real-world data the best I can for this query I'm having a hard time with.
Table HierarchySet:
HierarchySetId    ClientId
22                1866
23                1866
Table User:
UserId    UserName    ClientId
76        TestUser    1866
Table LinkTable:
LinkId    UserId    OrganisationId    HierarchySetId
20241     76        1822              23
20242     76        1877              23
Now, a note on the "LinkTable" "OrganisationId" field is that they are just a reference unrelated to any of these tables, it's just an example of how a user can have multiple rows in this table with the same UserId and HierarchySetId
What I'm trying to do here, is with specifying a HierarchySetId and a ClientId - I need to find all users for that ClientId that do not have rows in Linktable for that HiearchySetId - I'm absolutely STUMPED! - I'm using SQL Server 2008.
Thanks!!
Edit: Thanks Preet below, the final query came out like this:
SELECT * 
FROM User u
INNER JOIN HierarchySet h ON h.ClientId = u.ClientId
LEFT OUTER JOIN LinkTable l ON
    l.HierarchySetId = h.HierarchySetId
    AND u.UserId = l.UserId
WHERE l.HierarchySetId IS NULL and l.UserId IS NULL
AND u.ClientId = xxxxxx
AND h.HierarchySetId = xxxxxx