Mysql – Unknown column in ‘where clause’ when using an alias for a subquery in MySQL

aliasMySQLwhere

This simple query:

select exam.id as exam_id,
(select count(*) from dataset where dataset.id = exam.id) as n_dataset
from exam
where n_dataset = 0

returns the following error:

ERROR 1054 (42S22) at line 1: Unknown column 'n_dataset' in 'where clause'

Why?

Best Answer

Because the order of operations with the WHERE clause is applied first before the SELECT operator. So n_dataset doesn't exist yet when the WHERE clause is being applied.

You would need to use COUNT() as a window function (version of MySQL depending, you should update your tags to include the version) to get the results you're looking for.

Or you can create your COUNT() query in a CTE first, then re-join to your table later on.

But interpreting your logic further, it sounds like your actual end goal is to get all exams that don't have any correlating datasets. You can accomplish that a lot simpler with a LEFT JOIN this way:

select distinct exam.id as exam_id, 0 as n_dataset
from exam
left join dataset
    on exam.id = dataset.id
where dataset.id is null

Related Solutions

MySQL error in where clause : Unknown column

Now, if you rewrite the query in a clear manner, you can see where's the troublesome column. From what I know of general SQL you can't use the alias of the column in the WHERE clause (but you can use it in the ORDER BY clause).

So you'd have to rewrite the query something like:

Select *
from (
    -- Your nice query without the have_discount filter
) as Aux
WHERE Aux.have_discount = 1
ORDER BY date_activated DESC
LIMIT 0,12

Mysql – Not able to update MySQL table using Oracle: Unknown column in ‘where clause’ {42S22,NativeErr = 1054}

by experience sometimes creating oracle tables by using ODBC produce errors.

so please try to do the following:

create the table manually on oracle db by using sql plus.
try to use capital letters for table and column names without double quotes (example: use TABLE_NAME not table_name or Table_Name)
try to run your application to check if any errors appears.

also in where clause you have to check it. try:

WHERE SRC."INT_ID" = REC.MYSQL_INT_ID;

does it works?

Best Answer

Related Solutions

MySQL error in where clause : Unknown column

Mysql – Not able to update MySQL table using Oracle: Unknown column in ‘where clause’ {42S22,NativeErr = 1054}

Related Question