SELECT
                                                    h.synxis_id     AS hotel_id,
                                                    h.name          AS hotel_name,
                                                    rm.code         AS room_code,
                                                    rm.name         AS room_name,
                                                    rm.description  AS room_description,
                                                    rm.image_url    AS room_image_url,
                                                    r.id            AS rate_id,
                                                    r.plan_code,
                                                    r.amount_before_tax,
                                                    r.amount_after_tax,
                                                    r.currency_code,
                                                    r.rate_date,
                                                    (
                                                        CONCAT(
                                                            'https://gc.synxis.com/rez.aspx?chain=5154&start=availresults&adult=1&hotel=' ,
                                                            h.synxis_id,
                                                            '&arrive=',
                                                            r.rate_date
                                                        )
                                                    ) as booking_url


                                                FROM hotel as h
                                                LEFT JOIN room AS rm
                                                ON h.id = rm.hotel_id
                                                INNER JOIN rate AS r
                                                ON (
                                                    rm.id = r.room_id AND
                                                    r.amount_before_tax = (
                                                        SELECT
                                                            MIN(amount_before_tax)
                                                        FROM rate
                                                        LEFT JOIN room
                                                        ON rate.room_id = room.id
                                                        LEFT JOIN hotel
                                                        ON room.hotel_id = hotel.id
                                                        WHERE
                                                            hotel.synxis_id = h.synxis_id AND
                                                            rate.rate_date >= '" . date('Y-m-d') . "'
                                                    )
                                                )

                                                WHERE
                                                h.synxis_id IN ($hotels)

                                                GROUP BY h.id