how to join 3 tables and sum columns with some null values

Question

ive search for this problems solution for a while and although ive found some similar answers i can not seem to put them toguether to get the result i want. My problem is as follows: I have 3 tables in sql server 2008 (dont know if that matters much)

Order (order_id, sales_id, Product_id,combo_id)
product(product_id, name,price)
combo(combo_id,name price)

it doesnt really matter what sales have. Now i wanna get a sum of all the price for a sale id but the thing is for each row one of bouth product_id or combo_id will be null and i cant seem to get a result from using an inner join between the 3 tables, i think im realy lost in this one so i would apreciate a lot any help i can get Ive also try the following with a partial good result

SELECT        SUM(ISNULL(Combo.price, 0)) AS Expr1
FROM            Order INNER JOIN
                         Combo ON Order.combo_id = Combo.combo_id
WHERE        (Order.sales_id = @id)

i wanna do something similar joinning the 3 tables but ive had no such luck. Thx again

What will be the third table does? it might affects your Result. — Jade
– Jade, Commented Dec 11, 2013 at 2:37

Jade · Accepted Answer · 2013-12-11 02:42:13Z

2

Try this

SELECT        SUM(ISNULL(Combo.price, 0)) AS Expr1, isnull(max(product.Price), 0.00) as ProdPRice
FROM            Order left outer join
                         Combo ON Order.combo_id = Combo.combo_id
                left outer join product on Order.Product_ID = product.Product_ID                      
WHERE        (Order.sales_id = @id)

answered Dec 11, 2013 at 2:42

Jade

2,9941 gold badge13 silver badges9 bronze badges

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

Comments

Szymon · Accepted Answer · 2013-12-11 02:29:06Z

2

If I undestand your need correctly, you want a sum from both product and combo. You can union the results from both

SELECT SUM(X.Expr1) AS Expr1
FROM
(
    SELECT        SUM(ISNULL(Combo.price, 0)) AS Expr1
    FROM            [Order] INNER JOIN
                         Combo ON [Order].combo_id = Combo.combo_id
    WHERE        ([Order].sales_id = @id) 

    UNION ALL

    SELECT        SUM(ISNULL(Product.price, 0)) AS Expr1
    FROM            [Order] INNER JOIN
                         Product ON [Order].Product_id = Product.Product_id
    WHERE        ([Order].sales_id = @id) 
) X

answered Dec 11, 2013 at 2:29

Szymon

43k16 gold badges99 silver badges115 bronze badges

Collectives™ on Stack Overflow

how to join 3 tables and sum columns with some null values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related