Error 1054: Column is unknown and running the same seperate query runs without problem-CodePudding

I have a query with three JOINS, in which two subqueries are involved.

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 query gives me the following error:

Error 1054: t3.idToken on clause is unknown.

However, the following query works:

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

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

Thanks for the help.

CodePudding user response：

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".