Unknown column ‘stockout_details.purchi’ in ‘on clause’

i’m using this query but i found this error Unknown column ‘stockout_details.purchi’ in ‘on clause’

SELECT sale_nag,stockin_details.purchi,
       COALESCE(stockin_details.sale_nag,0) + stockin_details.sale_nag   result
FROM stockin_details LEFT JOIN 
     (SELECT SUM(sale_nag) AS snag
      FROM stockout_details
      WHERE purchi='a12'
      GROUP BY marka,chalan,room,rack
     ) stockout_details
     ON stockin_details.purchi=stockout_details.purchi
WHERE stockin_details.purchi='a12'
GROUP BY marka,chalan,room,rack

Answer

You need to include the column in the subquery:

SELECT o.sale_nag, i.purchi,
       COALESCE(i.sale_nag,0) + i.sale_nag   result
FROM stockin_details i LEFT JOIN 
     (SELECT purchi, SUM(sale_nag) AS snag
      FROM stockout_details
      WHERE purchi = 'a12'
      GROUP BY marka, chalan, room, rack, purchi
     ) o
     ON i.purchi = o.purchi
WHERE i.purchi = 'a12'
GROUP BY i.marka, i.chalan, i.room, i.rack;

Note that I replaced the table aliases with more manageable aliases and qualified all column names.

That said, I’d be surprised if the query actually did anything useful — the result is only using values from one table.

Also, I would expect the join conditions to use all the columns in the aggregation. If you want further help with the query, ask a new question. Provide sample data, desired results, an explanation of what the code should do, and an appropriate database tag.