MYSQL JOINs returning more results than it should.

Answer 1

You are most likely counting several product_id's stocklevels multiple times.

One solution would be to pre-compute the stocklevel for each product_id and join this with your original query.

SELECT    p.id as product_id
          , p.description as description
          , SUM(col.quantity) as qty
          , sl.stocklevel as stocklevel
          , sup.name as supplier 
from      products as p 
          INNER JOIN (
            SELECT    product_id, SUM(stocklevel) as stocklevel
            from      stock 
            GROUP BY  product_id
          ) sl ON sl.product_id = p.product_id
          LEFT JOIN customerorderlines as col on col.product_id = p.id 
          LEFT JOIN customerorders as co on co.id = col.customerorder_id 
          LEFT JOIN stock as s on s.product_id = p.id 
          LEFT JOIN suppliers as sup on sup.id = p.supplier_id 
WHERE     co.orderdate BETWEEN '2009-07-01' AND '2009-08-01' 
          AND p.supplier_id = 51 
GROUP by  col.product_id, sl.stocklevel
ORDER by  SUM(col.quantity) DESC 

Answer 2

SELECT prod.id as product_id, prod.description as description, 
 SUM(col.quantity) as qty, SUM(sup.stocklevel) as stocklevel, 
 sup.name as supplier, sup.id
 FROM product prod
 INNER join suppliers sup as sup.id = prod.supplier_id
 LEFT join stock stk as stk.product_id = prod.id
 LEFT JOIN customerorderlines as col on col.product_id = prod.id
 LEFT JOIN customerorders as co on co.id = col.customerorder_id
 WHERE co.orderdate BETWEEN '2009-07-01' AND '2009-08-01'
 GROUP BY sup.id, prod.id
 ORDER by SUM(col.quantity) DESC