Problem with multi-table MySQL query

Question

I have 3 tables. Here is the relevant information needed for each.

items

prod_id
order_id
item_qty
primarykey is prod_id

orders

order_id
order_date
order_status
acct_id
primary-key is order_id key is acct_id

accounts

acct_id
is_wholesale primary-key is acct_id

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!

Answer 1

Everything looks OK. Your query works correctly.

Your problem came from checking whether your query worked. You wrote another query that you believed summed both is_wholesale = 0 and is_wholesale = 1:

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;

However this query contains an error. Changing the LEFT JOIN to INNER JOIN fixes the query. It is also good practice to write INNER JOIN when that is what you mean, as OMG Ponies pointed out in a comment.

Answer 2

Can order_status be NULL? If so, you'll need to say (order_status <> 5 OR order_status IS NULL).