Sintetizzando tutto:

codice:
SELECT hot.*, sconto FROM hotels AS hot


LEFT JOIN (SELECT cam.hotel_id, cam.id_camera AS roomy, IFNULL(p.promo_sconto, 0) AS sconto FROM camere AS cam 
           
LEFT JOIN (SELECT promo_id, promo_sconto FROM promo WHERE promo_dal >= '2015-06-25' AND promo_al <= '2015-06-26' AND numero_ospiti = '1') AS p ON p.id_camera = cam.id_camera) AS rom ON hot.hotel_id = rom.hotel_id


WHERE hot.hotel_city = 'Adrano' AND disponibles >= '1'
mi da il seguente errore:

#1054 - Unknown column 'p.id_camera' in 'on clause', non capisco perchè