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

CodePudding user response：

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