SQL Select on priority of not exist

I'm quite confused as to how I'd structure a query I need:

select distinct NodeID from GroupsTable
                    where GroupID in
                    (
                        select GroupID from UserGroupsTable
                        where UserID = @UserID
                    )

I need to开发者_如何学Go select NodeID from GroupsTable where the User belongs to multiple groups, UNLESS any group that the user belongs to does not have that NodeID.

The above code only selects a NodeID from GroupsTable where it apears in any one of the users groups.

So:

Group|Node

1|A

1|B

2|A

I need only B to be selected for example.

Any Ideas?

declare @UserID int
select G.NodeID
from GroupsTable G
inner join
(
    select GroupID, count(*) over () GroupCount
    from UserGroupsTable
    where UserID = @UserID
) UG on UG.GroupID = G.GroupID
GROUP BY G.NodeID, UG.GroupCount
HAVING COUNT(*) != UG.GroupCount OR UG.GroupCount = 1
-- count=1 is special. it will always equal groupcount, but let it through

(assuming this is SQL Server and version >= 2005 for using the count(*) over)

@answer HAVING condition changed following comments

You can use a having clause to ensure that the count of groups per node is less than the count of all groups the user is in:

select  g.NodeID
from    GroupsTable g
join    UserGroupsTable ug 
on      g.GroupID = ug.GroupID
where   ug.UserID = 1
group by
        ug.UserID
,       g.NodeID
having  COUNT(distinct g.GroupID) < 
        (
        select  COUNT(distinct GroupID) 
        from    UserGroupsTable ug2 
        where   ug2.UserID = ug.UserID
        )