SELECT r.id AS room_id,
       GROUP_CONCAT( DISTINCT CONCAT(a.name, '*', a.icon) ORDER BY a.name SEPARATOR '|') AS attractions,
       GROUP_CONCAT(DISTINCT CONCAT(rp.id, '*', rp.price) ORDER BY rp.id SEPARATOR '|') AS price_list
  FROM room r LEFT JOIN property p             ON r.property_id=p.id
              LEFT JOIN property_attraction pa ON pa.property_id=p.id
              LEFT JOIN attraction a           ON a.id=pa.attraction_id AND (a.is_active=1 OR a.is_active IS NULL)
              LEFT JOIN room_price rp          ON rp.room_id=r.id
  WHERE pa.attraction_id IN(1,9)
  GROUP BY r.id
  HAVING count(distinct pa.attraction_id)>=2 -- albo = 2 jeśli (a)
  ORDER BY p.is_promoted DESC
  LIMIT 0, 50;

[SQL] pobierz, plaintext 
SELECT r.id AS room_id,
       GROUP_CONCAT( DISTINCT CONCAT(a.name, '*', a.icon) ORDER BY a.name SEPARATOR '|') AS attractions,
       GROUP_CONCAT(DISTINCT CONCAT(rp.id, '*', rp.price) ORDER BY rp.id SEPARATOR '|') AS price_list
  FROM room r LEFT JOIN property p ON r.property_id=p.id
              LEFT JOIN property_attraction pa ON pa.property_id=p.id
              LEFT JOIN attraction a ON a.id=pa.attraction_id AND (a.is_active=1 OR a.is_active IS NULL)
              LEFT JOIN room_price rp ON rp.room_id=r.id
  WHERE pa.attraction_id IN(1,9)
  GROUP BY r.id
  HAVING count(DISTINCT pa.attraction_id)>=2 -- albo = 2 jeśli (a)
  ORDER BY p.is_promoted DESC LIMIT 0, 50;
[SQL] pobierz, plaintext