0

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?

  • You can't call a field twice in this way. Can you give sample of your data ? – Leehan Jan 14 at 15:03
  • You should consider the COALESCE(...) function with your accepted answer - makes for nicer presentation! – Vérace Jan 15 at 6:29
1

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
  • See my comment to the OP above - HTH! – Vérace Jan 15 at 6:30
1

You can use simply HAVING in MySQL

CREATE TABLE exam ( id int)
CREATE TABLE dataset ( id int)
select exam.id as exam_id,
(select count(*) from dataset where dataset.id = exam.id) as n_dataset
from exam
HAVING n_dataset = 0
exam_id | n_dataset
------: | --------:

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.