SQLTeam.com | Weblogs | Forums

Compare Values in Same Table


#1

I would like to compare values within the same table so see if there are any price discrepancies between locations. I am brain farting on what to do.....

Example:
Item ID --- Store Price
12345 ----- 1 ----- 20.00
12345------ 2 ----- 18.50
ABCD ----- 1 ------ 5.00
ABCD ----- 2 ------ 5.00

I would like to know that there is a discrepancy in prices for item 12345. Anyone have any ideas?


#2

select *
from table1 t1
join table1 t2
on t1.itemid = t2.itemid
where t1.price <> t2.price


#3

Minor point: where t1.price < t2.priceOtherwise you get double the entries: X <> Y and Y <> X


#4

Thank you. This worked fairly well. I have around 75 stores, so I may need to do some fine tuning, but this definitely helped get me going in the right direction.


#5
SELECT tn.*
FROM table_name tn
WHERE [Item ID] IN (
    SELECT [Item ID]
    FROM table_name
    GROUP BY [Item ID]
    HAVING MIN(Price) <> MAX(Price) )