Why is the price column is unknown in the following sql query?

Question

I have the following table structures:

Course(course_id, course_name, language, course_price, create_date, average_rating, category, course_description, certificate_price, course_creator_id)

Person(person_id, username, email, name, surname, password, date_of_birth)

Discount(discount_id, discounted_course_id, allower_course_creator_id, is_allowed, start_date, end_date, percentage)

When I try to filter the courses whose price is >= 10, I wrote price >= 10 because in the SELECT clause, there is CASE-ELSE statement which ends with "AS price".

Why do I get "Unknown column 'price' in 'where clause'" error when I query this string?

SELECT C.course_id, C.course_name, C.language, C.average_rating, 
       C.category, P.name, P.surname, D.percentage, 
       D.start_date, D.end_date, D.is_allowed, 
      (CASE WHEN CURRENT_DATE <= D.end_date AND CURRENT_DATE >= D.start_date AND D.is_allowed THEN 
      C.course_price * (( 100 - D.percentage ) / 100) ELSE C.course_price END) as price 
FROM course C LEFT OUTER JOIN discount D ON C.course_id = D.discounted_course_id 
              LEFT JOIN person P ON 
              C.course_creator_id = P.person_id WHERE price >= 10 
              AND average_rating >= '30' AND average_rating <= '40' AND language = 'English' 
              AND category = 'Finance'

I want to filter according to the price, however, this way, I get the error.

Hint: [MySQL query / clause execution order](https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order) — Zhorov, May 10 '21 at 08:08
Hint 2: MySQL special trick, use HAVING clause for column alias conditions. — jarlh, May 10 '21 at 08:17

Nimal V · Answer 1 · 2021-05-11T01:44:13.633

2

Here Price is the name aliased in select clause. As per Mysql Clause Execution order where clause will be executed before select clause. ie,while executing where clause price will not be defined so it will unknown to where clause. In such cases you can use having clause instead of where clause because having clause will be executed after select clause.

edited May 11 '21 at 01:44

answered May 10 '21 at 08:29

Nimal V

166
1
4

Gordon Linoff · Accepted Answer · 2021-05-10T12:37:01.627

You cannot use price in the WHERE clause because it is defined in the SELECT.

The traditional SQL solutions are subqueries, CTEs, or a lateral join (which MySQL does not support -- yet). However, MySQL offers an alternative solution, which is to use the HAVING clause even though the query is not an aggregation query:

SELECT C.course_id, C.course_name, C.language, C.average_rating, 
       C.category, P.name, P.surname, D.percentage, 
       D.start_date, D.end_date, D.is_allowed, 
      (CASE WHEN CURRENT_DATE <= D.end_date AND CURRENT_DATE >= D.start_date AND D.is_allowed
            THEN C.course_price * (( 100 - D.percentage ) / 100)
            ELSE C.course_price
       END) as price 
FROM course C LEFT OUTER JOIN
     discount D
     ON C.course_id = D.discounted_course_id LEFT JOIN
     person P
     ON C.course_creator_id = P.person_id
WHERE average_rating >= '30' AND average_rating <= '40' AND
      language = 'English' AND category = 'Finance'
HAVING price >= 10;

Note that you are comparing average_rating to a string. I strongly suspect that average_rating is really a number, so you should be using:

WHERE average_rating >= 30 AND average_rating <= 40 AND
      language = 'English' AND category = 'Finance'

Is there a chance that this advice already exists on Stack Overflow ...a chance that you yourself have already supplied this exact insight of using HAVING? Can you help Stack Overflow to become a better resource by consolidating related/identical content? This is where SMEs can be most powerful in helping Stack Overflow. — mickmackusa, May 11 '21 at 01:54

Why is the price column is unknown in the following sql query?

2 Answers2