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!

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.