asked on

Query Help

Hi All - need help writing a query for MSSQL - cannot seem to get it right

I have a table of orders o

order_id int pk
sub_total money

I have another table of lineitems l

lineitem_id int pk
order_id fk to o.orders_id
linetotal money

An there may be many lineitems in an order

I am trying to write a query that finds any order that has a sub total that is <> to the sum of its lineitems (linetotals).

ASKER CERTIFIED SOLUTION

Simone B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

You can use a <> in a JOIN clause to find non-matches.

Copy-paste the below T-SQL into your SSMS, execute to verify it works for your question, then modify to meet your needs.

CREATE TABLE orders (order_id int, sub_total money)
CREATE TABLE lineitems (lineitemid int, order_id int, sub_total money)
GO

INSERT INTO orders (order_id, sub_total)
VALUES (1, 100), (2, 200), (3, 300) 

INSERT INTO lineitems (lineitemid, order_id, sub_total) 
VALUES 
	-- 1 is good 
	(1, 1, 30), 
	(2, 1, 70), 
	-- 2 is bad
	(3, 2, 42), 
	(4, 2, 99), 
	(5, 2, 86), 
	-- 3 is good
	(6, 3, 100), 
	(7, 3, 75), 
	(8, 3, 125) 

SELECT o.order_id, o.sub_total, l.sub_total
FROM orders o
   JOIN (SELECT order_id, SUM(sub_total) as sub_total FROM lineitems GROUP BY order_id) l ON o.order_id = l.order_id AND o.sub_total <> l.sub_total

Open in new window

ccleebelt

ASKER

Ah. I was so close. Thanks!

Anthony Perkins

I would do it this way:

SELECT  o.order_id,
        o.sub_total
FROM    orders o
        INNER JOIN (
                    SELECT  order_id,
                            SUM(linetotal) Sumlinetotal
                    FROM    lineitems
                    GROUP BY order_id
                   ) l ON o.order_id = l.order_id
WHERE   o.sub_total <> l.Sumlinetotal

Open in new window

Anthony Perkins

The reason I think my solution is better is because I see no reason to do a GROUP BY on the orders table.