CODEWITHSUNDEEP
sqloracle-databaseselect
TJE97
TJE97

Reputation: 67

SQL Check for multiple matching columns

If I have a table Q with columns: IDNUMBER, A, B, C, D, E, F, G, H;

Each of the letter columns contain numbers but are nullable. Assume there are thousands of rows and I want to find all of the rows that Do Not have matching data for each IDNUMBER (A==B==C==D==E==F==G==H). However, I want to completely ignore null values.

The table could look like this (n means null):

IDNUMBER |  A |  B  | C  | D |  E |  F |  G 
---------+----+-----+----+---+----+----+----
  100    |  6 |  6  | 6  | 6 |  6 |  6 |  6 
  101    |  n |  n  | n  | n |  5 |  5 |  5
  102    |  3 |  3  | 3  | 4 |  3 |  3 |  3 
  103    |  3 |  n  | 5  | n |  5 |  n |  5

I would want to return the rows with IDNUMBERs 102 and 103 because their NON-NULL values do not match. I do not want to return the rows with IDNUMBERs 100 and 101 because their NON-NULL values match.

Can someone provide a SELECT statement that would work for this?

Thank you!

P.S. I'm using Oracle SQL Developer 4.02.15.21

Upvotes: 1

Views: 231

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Oh, the easiest way would seem to be:

select q.*
from q
where greatest(a, b, c, d, e, f, g) <> least(a, b, c, d, e, f, g);

Alas, this doesn't work because of NULL values. This can make it challenging. Here is an approach:

select q.*
from q
where greatest(coalesce(a, b, c, d, e, f, g),
               coalesce(b, c, d, e, f, g, a),
               coalesce(c, d, e, f, g, a, b),
               coalesce(d, e, f, g, a, b, c),
               coalesce(e, f, g, a, b, c, d),
               coalesce(f, g, a, b, c, d, e),
               coalesce(g, a, b, c, d, e, f)
              ) <>
      least(coalesce(a, b, c, d, e, f, g),
            coalesce(b, c, d, e, f, g, a),
            coalesce(c, d, e, f, g, a, b),
            coalesce(d, e, f, g, a, b, c),
            coalesce(e, f, g, a, b, c, d),
            coalesce(f, g, a, b, c, d, e),
            coalesce(g, a, b, c, d, e, f)
           ) ;

This compares each value, putting in some non-NULL value if available in the case of NULL. You don't say what to do if all values are NULL. In that case, nothing will be returned with this expression.

Upvotes: 5

Related Questions