Include missing items in query

[severndigital]

So .. i have this query

 

SELECT prd.name, prd.short_desc, prd.qty_in_stock, SUM( ordprods.qty ) AS order_qtys, ord.datemade
FROM prod_list prd
INNER JOIN order_prods ordprods ON prd.id = ordprods.prodid
INNER JOIN order_list ord ON ordprods.orderid = ord.orderid
WHERE ord.datemade
BETWEEN '2008-11-01'
AND '2008-11-30'
GROUP BY prd.id
ORDER BY order_qtys ASC

 

The only problem is it does not include products from the prod_list table that were not part of an order.

 

Is there away to call this query and get the results for all products from prod_list even if they were not part of an order from order_prods

 

Thanks,

-C

 

[severndigital]

* UPDATE *

 

SO far i got this

 

SELECT prd.name, prd.short_desc, SUM( op.qty ) AS total_usage
FROM prod_list prd
LEFT JOIN order_prods op ON prd.id = op.prodid
WHERE prd.active = '1'
GROUP BY prd.id
LIMIT 0 , 30

 

This includes the NULL value products, but I can't seem to figure out how to implement the daterange on the third table (order_list)

 

Once I include that table, It removes all the null values.

 

Any ideas??

[xtopolis]

I'd be willing to play with this if you (correctly) posted structure dumps and some (a few rows only!) sample data.  Otherwise, I can't visualize it well enough based on your info.

[fenway]

You need to switch to left join for all the related tables... and be careful about getting back non-aggregated columns with group by.