Trying to join two sql statement

Question

I would like to join Query 1 and Query 2 on TripId.

Query 1

SELECT tblTrips.TripId,tblVehicles.VehicleNo FROM tblTrips INNER JOIN tblVehicles ON tblTrips.VehicleId = tblVehicles.VehicleId

Query 2

;with T1 as (
SELECT        tblTrips.TripId, tblTripDeductions.Amount, CONVERT(VARCHAR(400),tblDeductionTypes.DeductionType+' - '+tblTripDeductions.Description+' - '+ CONVERT(VARCHAR(24),tblTripDeductions.Amount)) as DeductionFor
FROM            tblTrips INNER JOIN
                         tblTripDeductions ON tblTrips.TripId = tblTripDeductions.TripId INNER JOIN
                         tblDeductionTypes ON tblTripDeductions.DeductionId = tblDeductionTypes.DeductionId
      )select **T1.TripId**, SUM(T1.Amount) as Amount, stuff((select '#','    ' + CONVERT(varchar(1000),T2.DeductionFor) from T1 AS T2 where T1.TripId = T2.TripId for xml path('')),1,1,'') [Description] from  T1
Group by TripId

First query's output is list of TripId and VehicleNo. Second query's output is list of TripId, Amount and description.

And my desire output is TripId, VehicleNo, amount and description.

Answer 1

Just merge the queries using CTE (didn't change/review your code, just formatted it for the sake of readability - input was pretty horrible to read)

;WITH T1 AS (
    SELECT tblTrips.TripId
         , tblTrips.DestinationDistrictId
         , tblTrips.VehicleId
         , tblTrips.No
         , tblVehicles.VehicleNo
         , tblTrips.CoachNo
         , CONVERT(VARCHAR(24), tblTrips.GoDate, 105) AS GoDate
         , tblTrips.GoTime
         , CASE WHEN tblTrips.IsCome=1 
                THEN CONVERT(VARCHAR(24), tblTrips.ComeDate, 105) 
                ELSE '-' 
           END AS ComeDate
         , CASE WHEN tblTrips.IsCome=1 
                THEN tblTrips.ComeTime 
                ELSE '-' 
           END AS ComeTime
         , CASE WHEN tblTrips.IsCome=1 
                THEN (SD.DistrictName + ' - ' + DD.DistrictName + ' - ' + SD.DistrictName) 
                ELSE (SD.DistrictName + ' - ' + DD.DistrictName) 
           END AS Destination
         , tblSupervisors.Name AS Supervisor
         , tblDrivers.Name AS Driver
         , tblTrips.AdvanceAmount
         , tblTrips.AdvanceDescription
    FROM tblTrips 
    INNER JOIN tblSupervisors ON tblTrips.SuperVisorId = tblSupervisors.SupervisorId 
    INNER JOIN tblDrivers ON tblTrips.DriverId = tblDrivers.DriverId 
    INNER JOIN tblDistricts SD ON tblTrips.StartDistrictId = SD.DistrictId
    INNER JOIN tblDistricts  DD ON tblTrips.DestinationDistrictId = DD.DistrictId
    INNER JOIN tblVehicles ON tblTrips.VehicleId = tblVehicles.VehicleId
)
, Q1 AS (
    SELECT T1.TripId
         , SUM(T1.Amount) AS Amount
         , STUFF((
                SELECT '#', '    ' + CONVERT(VARCHAR(MAX), T2.DeductionFor) 
                FROM T1 AS T2 
                WHERE T1.TripId = T2.TripId FOR XML PATH(''))
            ,1,1,'') AS [Description] 
    FROM T1
    GROUP BY TripId
)
, Q2 AS (
    SELECT tblTrips.TripId
         , tblTripDeductions.Amount
         , CONVERT(VARCHAR(400), tblDeductionTypes.DeductionType + ' - ' + tblTripDeductions.Description + ' - ' + CONVERT(VARCHAR(24), tblTripDeductions.Amount)) AS DeductionFor
    FROM tblTrips 
    INNER JOIN tblTripDeductions ON tblTrips.TripId = tblTripDeductions.TripId 
        INNER JOIN tblDeductionTypes ON tblTripDeductions.DeductionId = tblDeductionTypes.DeductionId
)
SELECT * 
FROM Q1 
INNER JOIN Q2 ON Q1.TripId = Q2.TripId

Answer 2

The Syntax for WITH (Common Table Expressions) allows you to create multiple CTE's.

Using that you can turn your final part of Query2 in to a CTE (Which I'll name Query2) and your query for Query1 can also be made in to a CTE (which I'll name Query1).

Then, the final SELECT statement can simply join those two CTE's together.

;

WITH
  T1 as (
SELECT        tblTrips.TripId, tblTripDeductions.Amount, CONVERT(VARCHAR(400),tblDeductionTypes.DeductionType+' - '+tblTripDeductions.Description+' - '+ CONVERT(VARCHAR(24),tblTripDeductions.Amount)) as DeductionFor
FROM            tblTrips INNER JOIN
                         tblTripDeductions ON tblTrips.TripId = tblTripDeductions.TripId INNER JOIN
                         tblDeductionTypes ON tblTripDeductions.DeductionId = tblDeductionTypes.DeductionId
      )
,
  Query2 AS (
  select **T1.TripId**, SUM(T1.Amount) as Amount, stuff((select '#','    ' + CONVERT(varchar(1000),T2.DeductionFor) from T1 AS T2 where T1.TripId = T2.TripId for xml path('')),1,1,'') [Description] from  T1
Group by TripId
  )
,
  Query1 AS (
  <Your Code For Query1>
)

SELECT
  *
FROM
  Query1
INNER JOIN
  Query2
    ON Query1.TripID = Query2.TripID

I haven't don't anything to check your queries, as the layout that you have used isn't very readable.