patternsqlMinor
Find all values not present for other values of other column
Viewed 0 times
presentallcolumnforfindvaluesothernot
Problem
Let's suppose I have a table with integer columns
Now I need to get all the rows whose
I want to include only two sets (groups with the same
I have some (working) solution; I post it as an answer. However, there should be something more elegant - the query should be one and exploit the symmetricity, not two unioned queries. I thought of various solutions listed in this answer, but after some hour of playing with code I didn't find how to make anything better and working.
a and b (each pair (a,b) is unique) and following data:a | b
-------
1 | 2
1 | 3
1 | 4
1 | 5
2 | 1
2 | 3
2 | 4
3 | 1
3 | 3Now I need to get all the rows whose
b value is not contained in the set with some other a value (i.e. they appear just once in the union of rows with the 'a' values in question). From these I should get (1,2), (1,5) and (2,1).I want to include only two sets (groups with the same
a value; I want to use this as part of a function, where both a values are given as parameters) at a time, so I don't want to get (3,1) from the new data. I have some (working) solution; I post it as an answer. However, there should be something more elegant - the query should be one and exploit the symmetricity, not two unioned queries. I thought of various solutions listed in this answer, but after some hour of playing with code I didn't find how to make anything better and working.
Solution
If your pairs are unique, as in the example you gave, you want the values of b that appear only once, and this would be a solution:
You can see it in this fiddle http://sqlfiddle.com/#!15/c82d3/3
SELECT a,b
FROM tbl1
WHERE b in (
SELECT b
FROM tbl1
GROUP BY b
HAVING (count(b)) = 1
)You can see it in this fiddle http://sqlfiddle.com/#!15/c82d3/3
Code Snippets
SELECT a,b
FROM tbl1
WHERE b in (
SELECT b
FROM tbl1
GROUP BY b
HAVING (count(b)) = 1
)Context
StackExchange Database Administrators Q#74428, answer score: 4
Revisions (0)
No revisions yet.