CODEWITHSUNDEEP
sqlsql-server
jOE127
jOE127

Reputation: 123

SQL query to return a value based on holding a number of values in other corresponding columns

In the table below I am trying to run a query that returns a GroupId that has a number of corresponding NodeIds. For example if I have the nodeIDs 595,644,659 then GroupId 2 would be returned but not 3, seen as thought 3 corresponds to 644 and 595 but not 659. I don't know would I have to do some kind of join ? Wasn't too sure on how best to word the title, Thanks for any help.

GroupSchema_Id  GroupId ISchemeID   NodeId
1                     2 NULL            595
2                     2 NULL            644
3                     2 NULL            659
4                     2 NULL            662
5                     2 NULL            592
6                     2 NULL            626
7                     2 NULL            595
8                     3 NULL            644
9                     3 NULL            595

Upvotes: 1

Views: 31

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You don't need a join. You can do what you want with group by:

select groupId
from t
where nodeId in (595, 644, 659)
group by groupId
having count(distinct nodeId) = 3;

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Depending if the NodeID are unique for each group.

SELECT GroupId 
FROM YourTable
GROUP BY GroupID
HAVING SUM (CASE WHEN NodeID IN (595,644,659) 
                      THEN 1
                 ELSE 0
            END) = 3

Upvotes: 2

Related Questions