Trying to join two sql statement -

i join query 1 , query 2 on tripid.

query 1

select tbltrips.tripid,tblvehicles.vehicleno tbltrips inner join tblvehicles on tbltrips.vehicleid = tblvehicles.vehicleid

query 2

;with t1 ( select        tbltrips.tripid, tbltripdeductions.amount, convert(varchar(400),tbldeductiontypes.deductiontype+' - '+tbltripdeductions.description+' - '+ convert(varchar(24),tbltripdeductions.amount)) deductionfor            tbltrips inner join                          tbltripdeductions on tbltrips.tripid = tbltripdeductions.tripid inner join                          tbldeductiontypes on tbltripdeductions.deductionid = tbldeductiontypes.deductionid       )select **t1.tripid**, sum(t1.amount) amount, stuff((select '#','    ' + convert(varchar(1000),t2.deductionfor) t1 t2 t1.tripid = t2.tripid xml path('')),1,1,'') [description]  t1 group tripid 

first query's output list of tripid , vehicleno. second query's output list of tripid, amount , description.

and desire output tripid, vehicleno, amount , description.

the syntax with (common table expressions) allows create multiple cte's.

using can turn final part of query2 in cte (which i'll name query2) , query query1 can made in cte (which i'll name query1).

then, final select statement can join 2 cte's together.

;    t1 ( select        tbltrips.tripid, tbltripdeductions.amount, convert(varchar(400),tbldeductiontypes.deductiontype+' - '+tbltripdeductions.description+' - '+ convert(varchar(24),tbltripdeductions.amount)) deductionfor            tbltrips inner join                          tbltripdeductions on tbltrips.tripid = tbltripdeductions.tripid inner join                          tbldeductiontypes on tbltripdeductions.deductionid = tbldeductiontypes.deductionid       ) ,   query2 (   select **t1.tripid**, sum(t1.amount) amount, stuff((select '#','    ' + convert(varchar(1000),t2.deductionfor) t1 t2 t1.tripid = t2.tripid xml path('')),1,1,'') [description]  t1 group tripid   ) ,   query1 (   <your code query1> )  select   *   query1 inner join   query2     on query1.tripid = query2.tripid 

i haven't don't check queries, layout have used isn't readable.