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

Are promo and regular in separate tables?

CBredlow
– CBredlow

2013-02-12 17:43:31 +00:00
Commented Feb 12, 2013 at 17:43 — CBredlow
– CBredlow, Commented Feb 12, 2013 at 17:43
No, they are in the same table.

systemnate
– systemnate

2013-02-12 18:56:48 +00:00
Commented Feb 12, 2013 at 18:56 — systemnate
– systemnate, Commented Feb 12, 2013 at 18:56

Andrew Savinykh · Accepted Answer · 2013-02-12 19:51:32Z

5

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.

edited Feb 12, 2013 at 19:51

Andrew Savinykh

26.8k20 gold badges110 silver badges168 bronze badges

answered Feb 12, 2013 at 17:43

Beth

9,6171 gold badge26 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wilduck · Accepted Answer · 2013-02-12 20:56:17Z

1

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

edited Feb 12, 2013 at 20:56

answered Feb 12, 2013 at 17:43

Wilduck

14.2k13 gold badges63 silver badges91 bronze badges

2 Comments

Andrew Savinykh Over a year ago

I don't think that will run on MS Sql Server 2008 R2. What is USING ? Can be fixed like this: sqlfiddle.com/#!3/cc32f/4

Wilduck Over a year ago

@zespri ahh, yes. I didn't realize that MS SQL Server didn't have the USING construct. It's a simplififed syntax for equi-joins (en.wikipedia.org/wiki/Join_(SQL)#Equi-join). I'll edit my question to add that in.

Andriy M · Accepted Answer · 2013-02-13 09:37:47Z

1

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).

answered Feb 13, 2013 at 9:37

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Comments

Art · Accepted Answer · 2013-02-12 17:48:24Z

0

In addition... Judging by your example - looks like a table to me - you may use LAG() / LEAD() analytic functions if available in you version of SQL to compare data - prev or next row with current row values...

answered Feb 12, 2013 at 17:48

Art

5,8222 gold badges24 silver badges22 bronze badges

1 Comment

Oleksandr Fedorenko Over a year ago

FYI, LAG() / LEAD() analytic functions available in SQLServer2012

Oleksandr Fedorenko · Accepted Answer · 2013-02-12 19:39:22Z

0

Option with EXISTS subquery

SELECT *
FROM dbo.test3 t1
WHERE EXISTS (
              SELECT 1
              FROM dbo.test3 t2
              WHERE t2.PriceName = CASE WHEN t1.PriceName = 'PROMO_PRICE' THEN 'REGULAR_PRICE'
                                        WHEN t1.PriceName = 'REGULAR_PRICE' THEN 'PROMO_PRICE' END
                AND t1.Price = t2.Price                                                    
              )

Demo on SQLFiddle

edited Feb 12, 2013 at 19:39

answered Feb 12, 2013 at 19:32

Oleksandr Fedorenko

17k6 gold badges43 silver badges51 bronze badges

Collectives™ on Stack Overflow

SQL - Comparing Multiple Rows in Query

5 Answers 5

Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related