SQL - Comparing Multiple Rows in Query

Question

I have a pricing table for a retail software that holds a UPC for an item, a property code (i.e. "REGULAR_PRICE, PROMO_PRICE"), and a price.

example table:

400000320243 REGULAR_PRICE 80
400000320243 PROMO_PRICE 80
400000320250 REGULAR_PRICE 50
400000320250 PROMO_PRICE 40

I am trying to write a query to find where the PROMO_PRICE = REGULAR_PRICE for any UPCs and output a list of the UPCs where this condition holds.

I cannot figure out how to write this in SQL. I am using SQL Server 2008 R2.

Attempted pseudo code:

for each upc:
  if upc.regular_price = upc.promo_price:
      print upc

Solution 1

You could do that a bunch of ways. One way is to create two sets, one with upcs and regular prices, the other with upcs and promo prices, and join those two sets, like this:

select r.upc, r.price from
   (select upc, price from t where propertyCode = 'regular_price') r inner join
   (select upc, price from t where propertyCode = 'promo_price') p on
   r.upc = p.upc and
   r.price = p.price

You can try it on sqlfiddle.

Solution 2

An INTERSECT query will also work here:

SELECT upc, price
FROM atable
WHERE property_code = 'REGULAR_PRICE'
INTERSECT
SELECT upc, price
FROM atable
WHERE property_code = 'PROMO_PRICE'
;

SQL Fiddle Demo (uses @Beth's schema).

Solution 3

Assuming you're table is named Pricing and you have columns named upc, propertycode, and price, you could perform a self join on this data. It would look something like:

SELECT Reg.upc
FROM Pricing Reg JOIN Pricing Promo ON Reg.upc = Promo.upc
WHERE Reg.propertycode = 'REGULAR_PRICE' 
    AND Promo.propertycode = 'PROMO_PRICE'
    AND Reg.price = Promo.price