CODEWITHSUNDEEP
sql-server-2008t-sqldatasetcomparison
user1151923
user1151923

Reputation: 1872

get union of unique results from each set

If each circle presents a result set, how would I go about getting the results marked red (values only found in a single set) as a single set without having to compare each set with all the other sets?

Sets

Currently I am comparing each set with the other sets like this

(SELECT ID FROM MyTable WHERE Value = 1
    EXCEPT
    (SELECT ID FROM MyTable WHERE Value = 2
    UNION
    SELECT ID FROM MyTable WHERE Value = 3))
UNION
(SELECT ID FROM MyTable WHERE Value = 2
    EXCEPT
    (SELECT ID FROM MyTable WHERE Value = 1
    UNION
    SELECT ID FROM MyTable WHERE Value = 3))
UNION
(SELECT ID FROM MyTable WHERE Value = 3
    EXCEPT
    (SELECT ID FROM MyTable WHERE Value = 1
    UNION
    SELECT ID FROM MyTable WHERE Value = 2))

Upvotes: 1

Views: 36

Answers (1)

Nithin Gangadharan
Nithin Gangadharan

Reputation: 527

Assuming there exist only 1 combination of ID and Value.

SELECT ID
FROM MyTable
GROUP BY ID
HAVING COUNT(ID) = 1

As there can be only 1 combination of ID and Value, when you group the ID and you get the count more than 1, it means same ID is present in other result set as well hence it will not be displayed as the condition is HAVING COUNT(ID) = 1. If you have Values other than 1,2 and 3 then should put condition as

WHERE Value IN (1,2,3)

in the above query.

Upvotes: 2

Related Questions