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