Home Error 1054: Collumn is unknown and running the same seperate query runs without problem

Questions

Error 1054: Collumn is unknown and running the same seperate query runs without problem

June 25, 2022

I have a select query with 3 JOINS where two of them have a SELECT inside.

SELECT TK.id, TK.idtype,
   TK.twitter, TK.hive,
   TK.discord, TK.name,
   TK.description, TK.maxSupply,
   TK.symbol, TK.idTbGallery,
   TK.active, t1.*, t3.* FROM tb_tokensupply t1


JOIN (SELECT idToken, MAX(datetime) datetime FROM tb_tokensupply GROUP BY idToken) t2 ON t1.idToken = t2.idToken AND t1.datetime = t2.datetime
JOIN tb_tokens TK ON TK.id = t2.idToken
JOIN (SELECT MAX(price), MIN(price) FROM tb_tokensprice) t3 ON t1.idToken = t3.idToken

WHERE TK.id=84 AND TK.active = 1

This gives me an error 1054 saying t3.idToken on clause is unknown.

However, If I do the query

SELECT MAX(price), MIN(price) from tb_tokensprice WHERE idToken = 84

It works.

Can anyone help to figure out why the same query inside the JOIN it is not working?

Thanks for the help.

>Solution :

The error tells you the problem: "t3.idToken on clause is unknown" because you’re not bringing it in your selection. Update the "t3" subquery as follows:

(SELECT MAX(price), MIN(price), idToken FROM tb_tokensprice GROUP BY idToken) t3

The reason why SELECT MAX(price), MIN(price) from tb_tokensprice WHERE idToken = 84 works is because the WHERE clause is computed using the "tb_tokensprice" table.

On the other hand your bigger query just uses "tb_tokensprice" to compute MAX and MIN, then returns only those two fields and you’ll have no reference to "idToken".