Error: Unknown column 'r.consumable_id' in 'where clause'
Error No: 1054
SELECT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name 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(pd.name) LIKE '%drum%') AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.consumable_id ORDER BY p.sort_order ASC LIMIT 0,16