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