views:

90

answers:

1

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
+1  A: 
select * 
from user u
inner join hierarchyset h on h.clientid = u.clientid
left outer join linktable l on 
    l.hierarchsetid = h.hierarchsetid
 and u.userId = l.userId)
where l.hierarchsetid is null and l.userId isnull
Preet Sangha
This one worked flawlessly, thank you so much.
ShaunO
(fx. whistles and looks sheepish - it was a complete guess!)
Preet Sangha