Error: Unknown column 'r.consumable_id' in 'where clause'
Error No: 1054
SELECT *, AS name, p.image, AS manufacturer, AS stock, (SELECT AVG(r.rating) FROM shop_review r WHERE p.consumable_id = r.consumable_id GROUP BY r.consumable_id) AS rating FROM shop_consumable p LEFT JOIN shop_consumable_description pd ON (p.consumable_id = pd.consumable_id) LEFT JOIN shop_consumable_to_store p2s ON (p.consumable_id = p2s.consumable_id) LEFT JOIN shop_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN shop_stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE pd.language_id = '1' AND p2s.store_id = '0' AND ss.language_id = '1' AND (LCASE( LIKE '%44469727%') AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.consumable_id ORDER BY p.sort_order ASC LIMIT 0,16