sql server - How to write this SQL JOIN query with multiple conditions -

i have following query:

select a.reservationstayid,    c.createdon,    a.firstname + ' ' + a.lastname 'name',    b.propertycode as'property',    b.reservationstatus 'status',    d.rsl_rateplan 'rate plan code',    d.rsl_mealplan,    d.rsl_roomtype,    b.marketsegmentcode 'market segment',    e.travelagencytypecode 'source of business',    a.arrivaldate 'date of arrival',    a.departuredate 'date of departure',    (select datediff (day,arrivaldate,departuredate)      reservationstay c     c.reservationstayid = a.reservationstayid) 'nights',    c.adultcount 'adult',    c.childcount 'child',    b.guestcount 'guest',    d.rsl_reasonstay,    c.taprofileid,        c.pmsconfirmationnumber,    c.currencycode 'currency',    d.rsl_nationality 'nationality',    d.rsl_country 'country of residence',    d.rsl_totalroomrate,    e.name 'tour operator',    e.countrycode 'market code',        g.countrygroup 'market',    (select avg(rateamount)/1.15   reservationstaydate f   f.reservationstayid = a.reservationstayid) 'rate amount excl.vat',    c.taprofileid2,   e2.name 'booking origin (1)',   g2.countrygroup 'booking origin (2)'    guestnameinfo  join gueststaysummary b on a.reservationstayid = b.reservationstayid  left join reservationstay c on c.reservationstayid = b.reservationstayid  left join p5reservationlist d on d.rsl_code = b.reservationstayid  left join travelagency e on e.travelagencyid = c.taprofileid  left join market g on e.countrycode = g.countrycode  left join travelagency e2 on e2.travelagencyid = c.taprofileid2  left join market g2 on e2.countrycode = g2.countrycode   left join  (    select       reservationstayid,      datename(m,staydate) + ' ' + cast(datepart(yyyy,staydate) varchar) [mth],      count(*) [nights spent],      avg(rateamount) [rate],      min(createdon) createdon,      min(staydate) [dateofarrival],      max(staydate) [dateofdeparture]      reservationstaydate     group reservationstayid, datename(m,staydate) + ' ' + cast(datepart(yyyy,staydate) varchar)    ) x on x.reservationstayid = b.reservationstayid  a.primaryguest='+' , d.rsl_primaryguest='+' 

i need add join statement table called dbo.budget pull values 'pkgrevenue' , 'gadr' columns , add these 2 values in 2 new columns in output of above query. however, values of these 2 columns should joined based on these conditions: propertycode, month , market should match.

the dbo.budget has following structure:

propertycode    month          market    pkgrevenue      gadr    xxx          november 2014   uk         1,251,000      3,256 

you can enclose query in subquery , join budget table this:

select   ... -- here can put whatever columns want select (    -- query here ) t1  inner join budget b on .... 

or, use cte in same way this:

with cte (    -- query here ) select    c.*,   b.pkgrevenue,   b.gadr cte c inner join dbo.budget b  on c.propertycode = b.propertycode                            , c.month        = b.month                            , c.market       = c.market; 

as following:

with cte (         select         a.reservationstayid,        c.createdon,        a.firstname + ' ' + a.lastname 'name',        b.propertycode as'property',        b.reservationstatus 'status',        d.rsl_rateplan 'rate plan code',        d.rsl_mealplan,        d.rsl_roomtype,        b.marketsegmentcode 'market segment',        e.travelagencytypecode 'source of business',        a.arrivaldate 'date of arrival',        a.departuredate 'date of departure',        (select datediff (day,arrivaldate,departuredate)          reservationstay c         c.reservationstayid = a.reservationstayid) 'nights',        c.adultcount 'adult',        c.childcount 'child',        b.guestcount 'guest',        d.rsl_reasonstay,        c.taprofileid,            c.pmsconfirmationnumber,        c.currencycode 'currency',        d.rsl_nationality 'nationality',        d.rsl_country 'country of residence',        d.rsl_totalroomrate,        e.name 'tour operator',        e.countrycode 'market code',            g.countrygroup 'market',         (select avg(rateamount)/1.15         reservationstaydate f         f.reservationstayid = a.reservationstayid) 'rate amount excl.vat',         c.taprofileid2,        e2.name 'booking origin (1)',        g2.countrygroup 'booking origin (2)',        x.mth    -- <======== need select mth column      guestnameinfo      join gueststaysummary b on a.reservationstayid = b.reservationstayid      left join reservationstay c on c.reservationstayid = b.reservationstayid      left join p5reservationlist d on d.rsl_code = b.reservationstayid      left join travelagency e on e.travelagencyid = c.taprofileid      left join market g on e.countrycode = g.countrycode      left join travelagency e2 on e2.travelagencyid = c.taprofileid2      left join market g2 on e2.countrycode = g2.countrycode      left join      (        select           reservationstayid,          datename(m,staydate) + ' ' + cast(datepart(yyyy,staydate) varchar) [mth],          count(*) [nights spent],          avg(rateamount) [rate],          min(createdon) createdon,          min(staydate) [dateofarrival],          max(staydate) [dateofdeparture]          reservationstaydate         group reservationstayid, datename(m,staydate) + ' ' + cast(datepart(yyyy,staydate) varchar)        ) x on x.reservationstayid = b.reservationstayid      a.primaryguest='+' , d.rsl_primaryguest='+' ) select    c.*,   b.pkgrevenue,   b.gadr cte c inner join dbo.budget b  on c.propertycode = b.propertycode                            , c.mth          = b.month         -- <=== here put mth                            , c.market       = c.market