Unsure how to join tables 3 times to get the desired result

Question

I am trying to put together a query that gives me a report of number of sales of products and their stock level in 3 seperate locations (store branches)

The idea is to group together the results as follows:

    Prod.  | Loc1 Sales | Loc1 Stk | Loc2 Sales | Loc2 Stk | Loc3 Sales | Loc3 Stk 
    Item 1 | 323        | 34       | 23         | 7        |        119 | 54 
    Item 2 | 653        | 566      | 84         | 45       |        476 | 3434
    Item 3 | 121        | 23       | 300        | 5643     |        12  | 3434

I can start off by grabbing the products and the qty from one location:

SELECT    col.productname, sum(col.quantity) as qty
FROM      customerorderlines col
JOIN      customerorders co
ON        co.id = col.customerorder_id
WHERE     co.orderdate BETWEEN 'start date here' AND 'end date here'
AND       co.location_id = 1
GROUP BY  product_id
ORDER BY  qty DESC

Result:

productname | qty
item 1      | 146
item 2      | 74
item 3      | 63
item 4      | 49

Now I try to join the stock table on there:

SELECT     col.productname, sum(col.quantity) AS qty, sum(s.stocklevel) AS stocklevel
FROM       customerorderlines col
JOIN       customerorders co
ON         co.id = col.customerorder_id
JOIN       stock s 
ON         s.product_id = col.product_id
WHERE      co.orderdate BETWEEN 'start date here' AND 'end date here'
AND        co.location_id = 1
AND        s.location_id = 1
GROUP BY   col.product_id
ORDER BY   qty DESC

This, of course, doesnt work:

productname | qty | stocklevel
item 1      | 246 | 89123
item 2      | 98  | 18454
item 3      | 78  | 22565

Not only are the stocklevels wrong, it fluffs up the qty too. Still, I know this isnt the write way of going about it, so I try with a join:

SELECT     col.productname, sum(col.quantity) AS qty, sl.stocklevel
FROM       customerorderlines col
JOIN       ( SELECT
             product_id
             ,location_id
             ,SUM(stocklevel) AS stocklevel
             FROM stock 
             GROUP BY product_id
           ) sl
ON         sl.product_id = col.product_id
JOIN       customerorders co
ON         co.id = col.customerorder_id
JOIN       stock s 
ON         s.product_id = col.product_id
WHERE      co.orderdate BETWEEN 'start date here' AND 'end date here'
AND        co.location_id = 1
AND        s.location_id = 1
GROUP BY   col.product_id
ORDER BY   qty DESC

With this join, which works as a query on it own, I wait about 8 minutes before I kill the process because it just sits there.

Any suggestions?

Thanks in advance, Rob

Answer 1

Have you thought of creating a temporary table for intermediate results. Take a look here: http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm

Answer 2

SELECT  p.name,
        (
        SELECT  SUM(col.quantity)
        FROM    customerorder co
        JOIN    customerorderlines col
        ON      col.customerorder_id = co.id
        WHERE   co.localtion_id = 1
                AND co.orderdate BETWEEN @start AND @end
                AND col.product_id = p.id
        ),
        (
        SELECT  SUM(stocklevel)
        FROM    stock s
        WHERE   s.location_id = 1
                AND s.product_id = p.id
        ),
        (
        SELECT  SUM(col.quantity)
        FROM    customerorder co
        JOIN    customerorderlines col
        ON      col.customerorder_id = co.id
        WHERE   co.localtion_id = 2
                AND co.orderdate BETWEEN @start AND @end
                AND col.product_id = p.id
        ),
        (
        SELECT  SUM(stocklevel)
        FROM    stock s
        WHERE   s.location_id = 2
                AND s.product_id = p.id
        ),
        (
        SELECT  SUM(col.quantity)
        FROM    customerorder co
        JOIN    customerorderlines col
        ON      col.customerorder_id = co.id
        WHERE   co.localtion_id = 3
                AND co.orderdate BETWEEN @start AND @end
                AND col.product_id = p.id
        ),
        (
        SELECT  SUM(stocklevel)
        FROM    stock s
        WHERE   s.location_id = 3
                AND s.product_id = p.id
        )
FROM    product p