0
votes

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!

2
I assume you are looking for something other than the obvious need to change = 451 to = 464? - Mark Byers
Which of these columns is the primary key for each table, especially the items table? Maybe posting SHOW CREATE TABLE for each table will help. - Mark Byers
I'd change the LEFT JOIN to JOIN, but I don't see what's not working for you without sample data. - OMG Ponies
What are the values of $from_date and $to_date? Can you post the exact query that you are executing, after the variable substitutions have been made? - Mark Byers
What do you mean by 'it's not working correctly'? What is it doing? Wrong result? Too high or too low? Error? What error? - Mark Byers

2 Answers

0
votes

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

0
votes

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.