Problem with multi-table MySQL query
- by mahle
I have 3 tables. Here is the relevant information needed for each.
items
prod_id
order_id
item_qty
orders
order_id
order_date
order_status
acct_id
accounts
acct_id
is_wholesale
items is linked to order by the order_id and orders is linked to accounts via acct_id
I need to sum item_qty for all items where prod_id=464 and the order stats is not 5 and where the is_wholesale is 0 and the order_date is between two dates. Im struggling with this and would appreciate any help. Here is what I have but it's not working correctly:
SELECT SUM(items.item_qty) as qty
FROM items
LEFT JOIN orders ON orders.order_id = items.order_id
LEFT JOIN accounts on orders.acct_id = accounts.acct_id
WHERE items.prod_id =451
AND orders.order_date >= '$from_date'
AND orders.order_date <= '$to_date'
AND orders.order_status <>5
AND accounts.is_wholesale=0;
Again, any help would be greatly appreciated!
