Why does MySQL give me "Error Code: 1054. Unknown column 'x' in 'on clause'" when I've defined it in my SELECT?

Question

What is wrong with the following sql:

SELECT DATE(NOW())-INTERVAL seq.seq DAY sdate, u.order_type, IFNULL(c,0) c
FROM (
    SELECT 0 AS seq 
    UNION ALL SELECT 1 UNION ALL SELECT 2 
    UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6
) seq
LEFT JOIN (
    SELECT t.order_type, DATE_FORMAT(t.order_date, '%Y-%m-%d') as tdate, count(*) c
    FROM `order` t
    WHERE t.order_type IN (10,11)
    GROUP BY t.order_type, tdate
) u ON u.tdate = sdate

Why do I get Error Code: 1054. Unknown column 'sdate' in 'on clause'? Basically I'm trying to select a count of orders per day per order type. In case of no orders of a given type on a given day I want 0 as result:

| date       | order_type | count |
|------------|------------|-------|
| 2018-11-09 | 10         | 2     |
| 2018-11-09 | 11         | 0     |
| 2018-11-10 | 10         | 0     |
| 2018-11-10 | 11         | 0     |
| 2018-11-11 | 10         | 1     |
| 2018-11-11 | 11         | 2     |
| 2018-11-12 | 10         | 3     |
| 2018-11-12 | 11         | 2     |

(Obviously,) This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. Also: Read the manual: The MySQL select statement page addresses this & extensions to use select aliases elsewhere. — philipxy, Nov 14 '18 at 21:45
For clarity & avoiding parsing problems the general convention is to use explicit AS for select column aliases & to use implicit AS for from table aliases (correlation names). PS Re googling be aware that google treats double quotes as a special character asking for exact string match. Also learn about site: & other search features. — philipxy, Nov 14 '18 at 21:50
Possible duplicate of [Unknown Column In Where Clause](https://stackoverflow.com/questions/153598/unknown-column-in-where-clause) — philipxy, Nov 14 '18 at 21:54

Salman A · Accepted Answer · 2018-11-14T20:32:57.323

1

sdate is defined in SELECT clause and is therefore not available in the FROM clause (FROM is calculated waaaaaaaay before SELECT). You need to specify the calculation as-is in the join condition:

ON u.tdate = DATE(NOW())-INTERVAL seq.seq DAY

edited Nov 14 '18 at 20:32

answered Nov 14 '18 at 13:36

Salman A

262,204
82
430
521

2

Thanks for the insight. Works now, and I've learned something today. – madshov Nov 14 '18 at 14:12

Why does MySQL give me "Error Code: 1054. Unknown column 'x' in 'on clause'" when I've defined it in my SELECT?

1 Answers1