Search code examples
sqlequalityinequality

Comparing Similar Columns for Equality

I have a (simplified) table that is structured like so:

Table: ItemData

PK | ItemID | StoreFK | Retail
1  | 100101 | 1       | 4.99
4  | 100101 | 2       | 4.99
7  | 100101 | 3       | 0.99
2  | 100102 | 1       | 6.99
5  | 100102 | 2       | 6.99
8  | 100102 | 3       | 6.99
3  | 100103 | 1       | 7.99
6  | 100103 | 2       | 8.99
9  | 100103 | 3       | 9.99

I would like to return all the items that have a different retail at one or more stores:

Returns:

ItemID
100101 
100103

  • Item 100101 has a lower retail at store 3 then at store 1 & 2 it is returned.

  • Item 100103 has a different retail at each store location so it is returned.

  • Item 100102 has equality in it's retail at all three stores so it are not returned.

I am not new to SQL, but I am lost as to how to make this inequality check in an efficient manor. What is the best way to check for equality in one column based on groupings on another column?

Solution

  • With all due respect to Lieven, I prefer this:

    SELECT ItemID
FROM   ItemData
GROUP BY
       ItemID
HAVING COUNT(DISTINCT Retail)>1