HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Find all values not present for other values of other column

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
presentallcolumnforfindvaluesothernot

Problem

Let's suppose I have a table with integer columns 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 | 3


Now 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:

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.