SELECT distinct HM.HOTEL_CODE AS AliceHotelCode,
                (
                    SELECT VHM.VENDOR_HOTEL_CODE
                    FROM VD_VENDOR_HOTEL_MAPPING VHM
                             INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
                        AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
                        AND VVHM.DELETE_YN = 'N'
                             INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
                    WHERE 1 = 1
                      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
                      AND VHM.VENDOR_COMP_CODE = 110000
                    LIMIT 1
                ) AS LegacyHotelCode,
                HM.HOTEL_NAME_EN AS AliceHotelName,
                (
                    SELECT VHM.VENDOR_HOTEL_CODE
                    FROM VD_VENDOR_HOTEL_MAPPING VHM
                             INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
                        AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
                        AND VVHM.DELETE_YN = 'N'
                             INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
                    WHERE 1 = 1
                      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
                      AND VHM.VENDOR_COMP_CODE != 110000
                      AND VHM.VENDOR_COMP_CODE = 910001
                    LIMIT 1
                ) AS ExpediaCode,
                (
                    SELECT VHM.VENDOR_HOTEL_CODE
                    FROM VD_VENDOR_HOTEL_MAPPING VHM
                             INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
                        AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
                        AND VVHM.DELETE_YN = 'N'
                             INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
                    WHERE 1 = 1
                      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
                      AND VHM.VENDOR_COMP_CODE != 110000
                      AND VHM.VENDOR_COMP_CODE = 910002
                    LIMIT 1
                ) AS HotelbedCode,
                (
                    SELECT VHM.VENDOR_HOTEL_CODE
                    FROM VD_VENDOR_HOTEL_MAPPING VHM
                             INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
                        AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
                        AND VVHM.DELETE_YN = 'N'
                             INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
                    WHERE 1 = 1
                      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
                      AND VHM.VENDOR_COMP_CODE != 110000
                      AND VHM.VENDOR_COMP_CODE = 910003
                    LIMIT 1
                ) AS AgodaCode,
                (
                    SELECT VHM.VENDOR_HOTEL_CODE
                    FROM VD_VENDOR_HOTEL_MAPPING VHM
                             INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
                        AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
                        AND VVHM.DELETE_YN = 'N'
                             INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
                    WHERE 1 = 1
                      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
                      AND VHM.VENDOR_COMP_CODE != 110000
                      AND VHM.VENDOR_COMP_CODE = 910004
                    LIMIT 1
                ) AS DOTWCode,
                (
                    SELECT VHM.VENDOR_HOTEL_CODE
                    FROM VD_VENDOR_HOTEL_MAPPING VHM
                             INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
                        AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
                        AND VVHM.DELETE_YN = 'N'
                             INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
                    WHERE 1 = 1
                      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
                      AND VHM.VENDOR_COMP_CODE != 110000
                      AND VHM.VENDOR_COMP_CODE = 910005
                    LIMIT 1
                ) AS FitruumsCode,
                (
                    SELECT VHM.VENDOR_HOTEL_CODE
                    FROM VD_VENDOR_HOTEL_MAPPING VHM
                             INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
                        AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
                        AND VVHM.DELETE_YN = 'N'
                             INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
                    WHERE 1 = 1
                      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
                      AND VHM.VENDOR_COMP_CODE != 110000
                      AND VHM.VENDOR_COMP_CODE = 910016
                    LIMIT 1
                ) AS DidaCode,
                CC.COUNTRY_CODE AS CountryCode,
                CC.COUNTRY_NAME_EN AS CountryName,
                REGION.REGION_CODE AS CityCode,
                REGION.REGION_NAME_EN AS CityName,
                HM.ADDRESS_EN AS HotelAddressEn,
                CONCAT(
                        HM.STAR_RATING,
                        ' ',
                        CASE
                            WHEN HM.STAR_RATING <> '0.0'
                                AND HM.STAR_RATING <> '1.0' THEN 'Stars'
                            ELSE 'Star'
                            END
                ) AS StarRating,
                HM.LONGITUDE AS Longitude,
                HM.LATITUDE AS Latitude
FROM HO_HOTEL_MASTER HM
         INNER JOIN (
    SELECT DISTINCT HM.HOTEL_CODE
    FROM HO_HOTEL_MASTER HM
    WHERE 1 = 1
      AND EXISTS (
        SELECT 1
        FROM VD_VENDOR_HOTEL_CONTRACT VVHC
        WHERE VVHC.HOTEL_CODE = HM.HOTEL_CODE
    )
) D_HOTEL ON HM.HOTEL_CODE = D_HOTEL.HOTEL_CODE
         INNER JOIN BS_COUNTRY_CODE CC ON HM.COUNTRY_CODE = CC.COUNTRY_CODE
         LEFT JOIN BS_REGION_CODE REGION ON HM.REGION_CODE = REGION.REGION_CODE
WHERE 1 = 1
ORDER BY HM.COUNTRY_CODE;