0

Alias variable is not working while using in condition client != id

select *,str_to_date(invoice_date,'%d/%m/%Y') as date,invoice_to as id, 
    (select mob_no from client_info where mob_no = id ) as client
from client_invoice
where client != id
having str_to_date(invoice_date,'%d/%m/%Y') >= '$fdate'
   and str_to_date(invoice_date,'%d/%m/%Y') <= '$tdate'  
ORDER BY date DESC

Please help to solve this issue. Thanks in Advance.

Marc B
  • 356,200
  • 43
  • 426
  • 500
Dinesh G
  • 244
  • 1
  • 13
  • 3
    You can't reference an alias in the WHERE clause of the same query. Put it in the `HAVING` clause. – Barmar Jul 15 '14 at 16:54
  • http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html – Marc B Jul 15 '14 at 16:55
  • Conversely, since `invoice_date` is in the table, you can put the date tests in the `WHERE` clause. – Barmar Jul 15 '14 at 16:55
  • I don't see how this can return anything. The subquery returns `mob_no` that's the same as `id` and then calls it `client`, but then the `WHERE` clause says to return only rows where `client` is NOT the same as `id`. They directly contradict each other. Can you post some sample data and the expected result? – Barmar Jul 15 '14 at 17:00
  • possible duplicate of [How to use alias as field in mysql](http://stackoverflow.com/questions/6081436/how-to-use-alias-as-field-in-mysql) – Dinesh G Aug 13 '14 at 07:10
  • This question appears to be off-topic because RTFM – NDM Aug 13 '14 at 13:44

1 Answers1

2

You can't refer to aliases in the WHERE clause, you have to use HAVING for it.

SELECT *, str_to_date(invoice_date, '%d/%m/%Y') AS date,
       invoice_to AS id,
       (SELECT mob_no FROM client_info WHERE mob_no = invoice_to) AS client
FROM client_invoice
WHERE STR_TO_DATE(invoice_date, '%d/%m/%Y') BETWEEN '$fdate' AND '$tdate'
HAVING client != id
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Does it get an error? Did you see my comment above? – Barmar Jul 15 '14 at 17:06
  • I'll ask again, did you read my comment above? You're selecting `WHERE mob_no = id` and aliasing `mob_no` to `client`, then you have `WHERE client != id`. It can't be equal and not equal at the same time. – Barmar Jul 15 '14 at 17:18