Sum from three tables in ms sql

I am retrieving from three different tables

SELECT lc_orders_tb.lc_orderID, lc_orders_tb.lc_orderSubTotal, lc_orders_tb.lc_orderTotal, lc_orders_tb.lc_orderPlacedDate, lc_tb.lc_compName, lc_tb.lc_addCity, lc_orderQuantity_tb.lc_orde开发者_JAVA技巧rID, 
     sum(lc_orderQuantity_tb.lc_orderQuantity) 
AS    lc_orderQuantity 
FROM  lc_orders_tb, lc_orderQuantity_tb, lc_tb 
WHERE lc_orders_tb.lc_id=lc_tb.lc_id 
AND   lc_orderQuantity_tb.lc_orderID=lc_orders_tb.lc_orderID 
GROUP BY lc_orderQuantity_tb.lc_orderID

I want to show the above retrieved values in the gridview. Only lc_orderQuantity needs to be summed for a particular lc_orderID which must match against two tables, namely, lc_orders_tb and lc_orderQuantity_tb.

Could someone please help me out..

Thank you in advance.

---

;WITH q AS 
(
    SELECT lc_OrderID, lc_OrderQuantity = SUM(lc_orderQuantity)
        FROM dbo.lc_orderQuantity_tb
        GROUP BY lc_OrderID
)
SELECT 
    o.lc_orderID, 
    o.lc_orderSubTotal, 
    o.lc_orderTotal, 
    o.lc_orderPlacedDate, 
    lc.lc_compName, 
    lc.lc_addCity, 
    q.lc_orderQuantity
FROM
    dbo.lc_orders_tb AS o
INNER JOIN
    q ON o.lc_orderID = q.lc_OrderID
INNER JOIN
    dbo.lc_tb AS lc
    ON o.lc_id = lc.lc_id;

---

Along with Aaron's solution (which I prefer) you can also an inline queries in case you're not using a DB that doesn't support the WITH clause

Inline query in From

SELECT 
    o.lc_orderID, 
    o.lc_orderSubTotal, 
    o.lc_orderTotal, 
    o.lc_orderPlacedDate, 
    lc.lc_compName, 
    lc.lc_addCity, 
    q.lc_orderQuantity
FROM
    dbo.lc_orders_tb AS o
INNER JOIN
    (  SELECT lc_OrderID, lc_OrderQuantity = SUM(lc_orderQuantity)
        FROM dbo.lc_orderQuantity_tb
        GROUP BY lc_OrderID) q ON o.lc_orderID = q.lc_OrderID
INNER JOIN
    dbo.lc_tb AS lc
    ON o.lc_id = lc.lc_id;

or in the SELECT Clause

 SELECT 
        o.lc_orderID, 
        o.lc_orderSubTotal, 
        o.lc_orderTotal, 
        o.lc_orderPlacedDate, 
        lc.lc_compName, 
        lc.lc_addCity, 
        (SELECT SUM(lc_orderQuantity)
            FROM dbo.lc_orderQuantity_tb o
            WHERE o.lc_orderID = q.lc_OrderID
        ) as lc_orderQuantity
    FROM
        dbo.lc_orders_tb AS o
       INNER JOIN
        dbo.lc_tb AS lc
        ON o.lc_id = lc.lc_id;

You can also use CROSS APPLY in SQL 2005+

 SELECT 
        o.lc_orderID, 
        o.lc_orderSubTotal, 
        o.lc_orderTotal, 
        o.lc_orderPlacedDate, 
        lc.lc_compName, 
        lc.lc_addCity, 
        q.lc_orderQuantity
    FROM
        dbo.lc_orders_tb AS o
       INNER JOIN
        dbo.lc_tb AS lc
        ON o.lc_id = lc.lc_id
       CROSS APPLY 
       (SELECT 
            SUM(lc_orderQuantity) lc_orderQuantity
        FROM
             lc_orderQuantity_tb t
        WHERE o.lc_orderID  = t.lc_orderID) q