set a new column and be an unknown column in where clause (mysql)

Question

I have a problem with the WHERE CLAUSE in mysql query. I want to filter data from a column that I directly defined. this is my example table (named MEMBER) :

++++++++++++++++
title | name   |
++++++++++++++++
leaf  | andro  |
moon  | sheila |
rocks | sarah  |

I run this query :

SELECT *, IF( title = "moon", "fly","on earth") AS status FROM MEMBER;

and the result :

+++++++++++++++++++++++++
title | name   | status |
+++++++++++++++++++++++++
leaf  | andro  |on earth|
moon  | sheila |fly     |
rocks | sarah  |on earth|

But why when I added a WHERE CLAUSE to filter the status column at the end of the query it returns an error? this is the query:

SELECT *, IF( title = "moon", "fly","on earth") AS status 
FROM MEMBER 
WHERE status = "fly";

the error message is : #1054 - Unknown column 'status' in 'where clause'
the 'directly defined' column that i mean is status column, it's not exist in the table but i created/defined it in the query.
I have tried to create status column in the table and it has null value, so when i run the second query (that containing WHERE CLAUSE), it would returns zero result.
i know another way to filter the data, i can use this query :

SELECT *, IF( title = "moon", "fly","on earth") AS status 
FROM MEMBER 
WHERE title = "moon";

but I can't do the filter by using that query in my case. I have to filter the status column.
Please don't see this question as a fool question, because this is the case that i have. So, anybody has the alternatives?

You can not use column `alias` in `WHEN` clause. You can create an artificial sub-query, for example. — PM 77-1, Oct 21 '13 at 01:26
did you mean `WHERE`? what is artificial sub-query? and show me the example, please! :D — Oki Erie Rinaldi, Oct 21 '13 at 01:37
thanks! that's the answer of my "why" question that i looking for — Oki Erie Rinaldi, Oct 21 '13 at 01:43

score 2 · Answer 1 · answered Oct 21 '13 at 01:49

2

With sub-query:

SELECT * FROM
(
 SELECT *, IF( title = "moon", "fly","on earth") AS status  
 FROM MEMBER 
) sq 
WHERE status = "fly";

SQL Fiddle

answered Oct 21 '13 at 01:49

PM 77-1

12,933
21
68
111

yes, it works well too! but, i think it will spend more time to filter thousands of data. my program has thousands of data.. tq – Oki Erie Rinaldi Oct 21 '13 at 02:12
1

Just as an FYI: `HAVING` does its filtering **after** all the data was fetched and aggregated. – PM 77-1 Oct 21 '13 at 02:14
Ok! that's a new knowledge for me. tq – Oki Erie Rinaldi Oct 21 '13 at 02:35
2

This is a solution but has a minor performance issue with regards the use of a subquery. – Mark Oct 21 '13 at 02:42

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

Use the HAVING clause to set a condition on status field. The field status was aggregated outside of the table itself and hence, WHERE can do nothing.

SELECT 
    *, 
    IF( title = "moon", "fly","on earth") AS status 
FROM MEMBER 
HAVING status = "fly";

edited May 23 '17 at 12:28

Community

1
1

answered Oct 21 '13 at 01:38

hjpotter92

78,589
36
144
183

Wow! thank you! it works well.. I ever using `HAVING` in my previous program, but why do i forget!? :D – Oki Erie Rinaldi Oct 21 '13 at 01:47

set a new column and be an unknown column in where clause (mysql)

2 Answers2