If the subject is confusing, notice the last few lines of the code below:
SELECT spots.id AS spotId, (
SELECT (
physicals.unit_qty * units.qty
) AS last_real_qty
FROM physicals
LEFT OUTER JOIN units ON physicals.unit_id = units.id
WHERE spots.id = physicals.spot_id
ORDER BY physicals.created_at DESC
LIMIT 1
) AS last_physical, (
spots.unit_min_qty * units.qty
) AS min_stock
FROM spots
LEFT OUTER JOIN units ON spots.unit_id = units.id
WHERE last_physical <= min_stock
If I run it without the 'WHERE' clause, it comes out fine, with 'last_physical' and 'min_stock' columns.
If I run it with the 'WHERE' clause as above, MySQL complains about there not being any 'last_physical or 'min_stock' columns.
Is there something simple I'm overlooking? Thanks! -Suthern
no. WHERE matches against the 'from' and joined tables. try HAVING.
1 to 2 of 2