MySQL join based on the first 4 characters

Question

I'm trying to join a second table based on the first 4 characters of a field from the first table but I keep getting "Unknown column 'questions.first4' in 'on clause'" and I'm not sure why:

SELECT questions.id as id, question, answer, correct, SUBSTRING(question,1,4) as first4
FROM questions
LEFT JOIN answers ON answers.correct_answer = questions.first4
WHERE player_name = 'Alpha Squad'
ORDER BY id ASC

(I realize that the tables are structured inefficiently but unfortunately, I have to work with them as-is)

Tables: https://pastebin.com/Gur5ufXa

MySQL: https://pastebin.com/FLgWtQmY

Thanks in advance!

""Unknown column 'questions.first4' in 'on clause'" and I'm not sure why:" Because there isn't a column first4 within the questions table — Raymond Nijland, May 30 '18 at 15:26
"based on the first 4 characters" - the right way to fix this is to normalize your schema — symcbean, May 30 '18 at 15:37
I recommend that *all* column references be qualified with a table name (or with a table alias) even if the column names aren't ambiguous. As a favor to future readers, and to avoid the query breaking when a column of the same name is later added to another table in the query. (The reason for the error is that the column alias `first4` is not available for reference in the ON clause or WHERE clause. Just replace the invalid reference to `first4` with the expression `SUBSTRING(questions.question,1,4)` — spencer7593, May 30 '18 at 15:43
indeed @spencer7593 unaliased columns or unqualified columns can really cause problems when using co-related subquerys,, Besides it makes reading the query alot eazier because you directly know in which table the column is directing to thats why i always use fully qualified columns — Raymond Nijland, May 30 '18 at 15:45
The order of operation `FROM` `WHERE` `GROUP BY` `HAVING` `SELECT` `ORDER BY`. So when it sees `LEFT JOIN answers ON answers.correct_answer = questions.first4`, it doesn't know what it means. — Eric, May 30 '18 at 15:49
@RaymondNijland "`SUBSTRING(question,1,4)` .. can not use indexes" - `answers.correct_answer = SUBSTRING(question,1,4)` can use an index on `answers.correct_answer`. You can't even use an index from the left table in a LEFT JOIN. — Paul Spiegel, May 30 '18 at 15:50
@Eric - That has little to nothing to do with the logical or internal execution order. Column aliases are permited in GROUP BY and HAVING clauses but not in WHERE - though they all "come" before SELECT. — Paul Spiegel, May 30 '18 at 15:57
@PaulSpiegel It has everything to do with the execution order. In this example, OP alias column in `SELECT`, and try to use it in `JOIN` because he doesn't understand the order of execution. — Eric, May 30 '18 at 16:00
@Eric .. SQL is not a procedural language - There is **no** execution order. If they wanted, they would permit aliases in the WHERE clause as they did for GROUP BY and HAVING. But they decided not to do - Thats all. — Paul Spiegel, May 30 '18 at 16:06
@PaulSpiegel If you say there's no execution order in SQL, then you need to read this https://sqlbolt.com/lesson/select_queries_order_of_execution , because that's exactly the OP's problem, aliasing a column in `SELECT` then trying to use it in `FROM/JOIN` — Eric, May 30 '18 at 16:19

t.. · Accepted Answer · 2018-05-30T15:50:35.073

1

SELECT questions.id as id, question, answer, correct, SUBSTRING(question,1,4) as first4
FROM questions
LEFT JOIN answers ON answers.correct_answer = questions.correct
WHERE player_name = 'Alpha Squad'
ORDER BY id ASC

or this next one joins specifically on the ones with the matching first 4

SELECT questions.id as id, question, answer, correct, SUBSTRING(question,1,4) as first4
    FROM questions
    LEFT JOIN answers ON SUBSTRING(answers.correct_answer,1,4) = SUBSTRING(question,1,4)
    ORDER BY id ASC

edited May 30 '18 at 15:50

answered May 30 '18 at 15:40

t..

1,101
1
9
22

This works great, thanks so much for your help! Now I get it, finally. – ISOcrates May 30 '18 at 18:46
This works perfectly to find all of the answers per team that have incorrect answers `SELECT questions.id as id, question, answer, correct_answer, correct, SUBSTRING(question,1,4) as first4 FROM questions LEFT JOIN answers ON SUBSTRING(answers.question_number,1,4) = SUBSTRING(question,1,4) WHERE player_name = 'Alpha Squad' AND correct = 0` Is there a way to use UPDATE to set correct = 3 for all of the results that this query returns? – ISOcrates May 30 '18 at 19:27

score 0 · Answer 2 · answered May 30 '18 at 15:32

SELECT questions.id as id, question, answer, correct, SUBSTRING(question,1,4) as first4
FROM questions
LEFT JOIN answers ON answers.correct_answer = SUBSTRING(question,1,4) 
WHERE player_name = 'Alpha Squad'
ORDER BY id ASC

The problem is first4 doesnt exist at the moment you try to do the JOIN. Check here to see the order a select statment is processed.

Order Of Execution of the SQL query

MySQL join based on the first 4 characters

2 Answers2