SELECT hotels.*, isnull(trnow.propertyDescription,'') as propertyDescription, isnull(trnow.propertyInformation,'') as propertyInformation, a.hid FROM hotels left join hotelsTrnow Trnow ON hotels.trnow = Trnow.hotelID left join hid on hid.amcode = hotels.amcode collate latin1_general_cs_as left join htlfea..allhotels a ON a.hid = hid.hid collate latin1_general_cs_as WHERE trnow.hotelID = 1169499 and hotels.city = 'Edinburgh' and hotels.country = 'Scotland' ORDER BY nm;