set showplan_all on
select  distinct
        rm_key
        ,rm_code
from    rooms with(nolock)
    inner join mwPriceHotels ph with(nolock) on ph.sd_rmkey = rm_key
    inner join mwSpoData with(nolock) on    (mwSpoData.sd_tourkey = ph.sd_tourkey
                                            and mwSpoData.sd_hdkey = ph.sd_mainhdkey)
where   SD_CTKEYFROM = 1
AND     SD_CNKEY = 15
and     sd_ctkey = 3
order   by rm_code