Unknown column in where clause when nested selection

I have to get results from different tables in one query. But got the unknown column error.

SET sql_mode = '';

SELECT 
    e_mills.m_id, 
    e_mills.mill_name, 
    e_cities.city_name, 
    e_sugardata.mill_closing_stock, 
    (
        SELECT GROUP_CONCAT(mill_closing_stock SEPARATOR ', ') 
        FROM (
            SELECT mill_closing_stock 
            FROM `e_sugardata` 
            WHERE m_id = e_mills.m_id 
            GROUP BY date_added 
            ORDER BY date_added DESC 
            LIMIT 0,4
        ) AS mill_closing_stock
    ) AS stock_chart
FROM e_mills 
    INNER JOIN e_cities 
        ON e_mills.city_id = e_cities.city_id 
    INNER JOIN e_sugardata 
        ON e_sugardata.m_id = e_mills.m_id 
ORDER BY e_mills.province_id

ERROR

#1054 – Unknown column ‘e_mills.m_id’ in ‘where clause’

UPDATE

Same query is working on my computer but not on other computer

UPDATE 2 working on MySQL 8

MySQL 8 DB Fiddle

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

To avoid an error in MySQL 5.7, you need to get rid of the nested query with a limit. Instead of a LIMIT clause, you can use the SUBSTRING_INDEX function. Also, the GROUP BY clause is unnecessary, especially since it does not work without changing sql_mode if the column list of the SELECT clause does not match the GROUP BY list.

-- SET sql_mode = ''; -- no more needed.

SELECT 
  e_mills.m_id, 
  e_mills.mill_name, 
  e_cities.city_name, 
  e_sugardata.mill_closing_stock, 
  (
    SELECT 
      SUBSTRING_INDEX(
        GROUP_CONCAT(mill_closing_stock ORDER BY date_added DESC SEPARATOR ', '
        ), ', ', 4
      )
    FROM `e_sugardata` 
    WHERE m_id = e_mills.m_id 
  ) AS stock_chart
FROM e_mills 
    INNER JOIN e_cities 
        ON e_mills.city_id = e_cities.city_id 
    INNER JOIN e_sugardata 
        ON e_sugardata.m_id = e_mills.m_id 
ORDER BY e_mills.province_id

db<>fiddle: MySQL 5.7 and MySQL 8

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0