MySql Select Query - Getting errors from WHERE clause

Question

I'm new in MySQL.

SELECT
     receive_item.RECEIPT_ID,
     receive_item.ITEM_ID, 
     (receive_item.QTY - delivery_item.QTY) AS QtyRemained 
FROM receive_item
INNER JOIN delivery_item
ON receive_item.ITEM_ID = delivery_item.ITEM_ID 
AND receive_item.RECEIPT_ID = delivery_item.RECEIVE_ID

This is the result from the query above

RECEIPT_ID ITEM_ID  QTYRemained
    1         1         0
    2         2         0
    3         3         1
    4         4         0
    5         5         0

I want to show only the rows where a value of QTYRemained is not 0. Something like this

RECEIPT_ID ITEM_ID  QTYRemained
    3         3         1

I got an error when i added "From QTYRemained != 0" to the end of the select query above. Can somebody tell me why it's not working?

Error messages are useful. You should post yours as part of your question — Phil, Jan 24 '12 at 06:30
To add to what Phil was saying, are you getting an actual error message like the query is invalid or is it just returning 0 results? Because that matters. — bowlerae, Jan 24 '12 at 06:32
You forgot the WHERE clause. I don't see WHERE clause in your code. — itachi, Jan 24 '12 at 06:28

score 2 · Answer 1 · answered Jan 24 '12 at 06:29

2

Add at the very end

WHERE QtyRemained > 0

so...

SELECT
     receive_item.RECEIPT_ID,
     receive_item.ITEM_ID, 
     (receive_item.QTY - delivery_item.QTY) AS QtyRemained 
FROM receive_item
INNER JOIN delivery_item
ON receive_item.ITEM_ID = delivery_item.ITEM_ID 
AND receive_item.RECEIPT_ID = delivery_item.RECEIVE_ID
WHERE QtyRemained > 0

answered Jan 24 '12 at 06:29

bowlerae

924
1
14
37

1

You can't use "QtyRemained " alias in where – Jan 24 '12 at 06:38

score 2 · Answer 2 · 2012-01-24T06:37:29.623

2

Use having instead of where.

SELECT
     receive_item.RECEIPT_ID,
     receive_item.ITEM_ID, 
     (receive_item.QTY - delivery_item.QTY) AS QtyRemained 
FROM receive_item
INNER JOIN delivery_item
ON receive_item.ITEM_ID = delivery_item.ITEM_ID 
AND receive_item.RECEIPT_ID = delivery_item.RECEIVE_ID
having QtyRemained > 0

OR you can do like below:

SELECT
     receive_item.RECEIPT_ID,
     receive_item.ITEM_ID, 
     (receive_item.QTY - delivery_item.QTY) AS QtyRemained 
FROM receive_item
INNER JOIN delivery_item
ON receive_item.ITEM_ID = delivery_item.ITEM_ID 
AND receive_item.RECEIPT_ID = delivery_item.RECEIVE_ID
where (receive_item.QTY - delivery_item.QTY) > 0

edited Jan 24 '12 at 06:37

answered Jan 24 '12 at 06:30

No man... Not true always.here problem is with the alias. we cant't use in where. I tried and its working – Jan 24 '12 at 06:36
I'd be more inclined to create the query as a view (even an inline view). `HAVING` is *really* only for applying conditions to aggregate columns – Phil Jan 24 '12 at 06:39
@Phil see http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql sixteen upvote for same answer – Jan 24 '12 at 06:41
@Teez that particular question is referring to aggregate columns (`SUM` and `COUNT`) which would include a `GROUP BY` – Phil Jan 24 '12 at 06:44
1

I Think It will make implicit group by with primary key column if we will not specify. that will make no difference – Jan 24 '12 at 06:51
1

@bowlerae MySQL has a generous helping of pixie dust to take care of things like that. Being able to exclude non-aggregate columns from `GROUP BY` statements is another – Phil Jan 24 '12 at 06:57
Right phil. I aslo depends on table structure – Jan 24 '12 at 07:03

score 1 · Answer 3 · answered Jan 24 '12 at 06:35

1

Try:-

SELECT
     receive_item.RECEIPT_ID,
     receive_item.ITEM_ID, 
     (receive_item.QTY - delivery_item.QTY) AS QtyRemained 
FROM receive_item
INNER JOIN delivery_item
ON receive_item.ITEM_ID = delivery_item.ITEM_ID 
AND receive_item.RECEIPT_ID = delivery_item.RECEIVE_ID
group by receive_item.RECEIPT_ID
Having QtyRemained > 0

answered Jan 24 '12 at 06:35

Bajrang

8,361
4
27
40

You can't use "QtyRemained " alias in where – Jan 24 '12 at 06:38
1

where is the "where" ? I have not added any where. – Bajrang Jan 24 '12 at 06:40

score 1 · Accepted Answer · answered Jan 24 '12 at 06:47

Because I'm an old stick-in-the-mud and refuse to use HAVING without a GROUP BY clause, here's an alternative solution

SELECT
     ri.RECEIPT_ID,
     ri.ITEM_ID, 
     (ri.QTY - di.QTY) AS QtyRemained 
FROM receive_item ri
INNER JOIN delivery_item di
ON ri.ITEM_ID = di.ITEM_ID 
AND ri.RECEIPT_ID = di.RECEIVE_ID
WHERE ri.QTY > di.QTY

Pretty simple really

MySql Select Query - Getting errors from WHERE clause

4 Answers4