CODEWITHSUNDEEP
sqlsql-servert-sql
Dave
Dave

Reputation: 253

Compare value of a row with multiple rows in another table

Let's imagine I have two tables of orders which I want to compare to eachother. 

orders {
   id int, 
   price money
}

ordereditems {
   orderid int, 
   itemid int,
   price money
}

where orders.id = ordereditems.orderid

Naturally, this is a bad design since both tables don't need a price. However, how can I design a query to find out what rows in orders has a price, which mismatches with the sum of the pricecolumn in ordereditems?

Upvotes: 0

Views: 1606

Answers (4)

Brisingr
Brisingr

Reputation: 82

You can also use the below query to get order id's with mismatched price

SELECT o.id,MAX(o.price) AS price
FROM orders o
INNER JOIN ordereditems oi
ON o.id=oi.orderid
GROUP BY o.id
HAVING SUM(oi.price)<>MAX(o.price)

Upvotes: 0

Frank
Frank

Reputation: 2043

The easiest way to sum a value from two columns with prefer an existing one:

SELECT SUM(COALESCE(o.price, oi.price)) as price -- reorder columns for prefer
FROM orders o INNER JOIN -- if you are missing some rows use FULL JOIN
ordereditems oi ON oi.id = o.id

Upvotes: 0

knkarthick24
knkarthick24

Reputation: 3216

SELECT a.id,
       a.price,
       b.price,
       CASE
         WHEN a.price = b.price THEN 'PRICE MATCHED'
         ELSE 'PRICE NOT MATCHED'
       END AS RESULT
FROM   orders a
       INNER JOIN ordereditems b
               ON a.id = b.orderid
                  AND a.price IS NOT NULL

--orders has a price, which mismatches with the sum of the pricecolumn in ordereditems

SELECT a.id,
       a.price,
       b.total_price,
       CASE
         WHEN a.price = b.total_price THEN 'PRICE MATCHED'
         ELSE 'PRICE NOT MATCHED'
       END AS RESULT
FROM   orders a
       INNER JOIN (SELECT orderid,
                          Sum(price) AS total_price
                   FROM   ordereditems
                   GROUP  BY orderid) b
               ON a.id = b.orderid
                  AND a.price IS NOT NULL

Upvotes: 0

Johnny
Johnny

Reputation: 49

If I understand right:

SELECT * FROM orders o
LEFT JOIN OrderedItems ei
ON o.Price = ei.Price WHERE ei.Price is NULL

Upvotes: 1

Related Questions