Multitable query help

[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!

[griswolf]

what is the specific problem that translates to "not working correctly"?

[tyson.crouch]

Could you please provide the table structures in the CREATE TABLE format in the future. This will help is to understand your tables much easier. Also, please use the [ CODE ] tags in future.

As for your problem; Could you please provide more information as to what is happening with your query now as opposed to what you would like to happen

Regards,
TC