Problem with multi-table MySQL query

Posted by mahle on Stack Overflow See other posts from Stack Overflow or by mahle
Published on 2010-06-10T22:24:17Z Indexed on 2010/06/10 22:32 UTC
Read the original article Hit count: 154

Filed under:

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!

© Stack Overflow or respective owner

Related posts about mysql