Unknown column in fieldlist?

Question

SQL

THE CODE

SELECT `t1`.*, `t2`.* FROM (SELECT * FROM `matet` WHERE DATE(`date`) = CURDATE()) as `t1` INNER JOIN (
SELECT 
      	CASE WHEN t1.P2_06 <> t2.P2_06 THEN 0 ELSE 1 END +
      	CASE WHEN t1.P2_05 <> t2.P2_05 THEN 0 ELSE 1 END +
      	CASE WHEN t1.P2_04 <> t2.P2_04 THEN 0 ELSE 1 END +
      	CASE WHEN t1.P2_03 <> t2.P2_03 THEN 0 ELSE 1 END +
        ....
    AS columns_number
FROM matet
) AS t2 ON (t1.id <> t2.id)
 WHERE t2.columns_number > 3

ERROR
Unknown column 't1.P2_06' in 'field list'
How to solve?

Answer 1

You cannot, generally speaking, refer from a subquery to tables external to it.
Although mysql 8.0.14 added LATERAL JOIN , you can try