Sql queries combine them into 1 .. Help needed

Question

Select TourDateRange.Detail,                
            (select top 1 TourID from TourDates where TourDateRangeID=TourDateRange.ID)as TourID,
            (select top 1 TourDateRangeID from TourDates where TourDateRangeID=TourDateRange.ID)as TourDateRangeID,
            (select top 1 StartDate from TourDates where TourDateRangeID=TourDateRange.ID)as StartDate,
            (select top 1 StartDate from TourDates where TourDateRangeID=TourDateRange.ID order by ID desc)as EndDate,
            (select top 1 AdultPrice from TourDates where TourDateRangeID=TourDateRange.ID)as AdultPrice,
            (select top 1 ChildPrice from TourDates where TourDateRangeID=TourDateRange.ID)as ChildPrice,
            (select top 1 ID from TourDates where TourDateRangeID=TourDateRange.ID)as TourDatesID
        From TourDateRange
        Where TourDateRange.TourID = @sTourID
        Order By TourDateRange.ID Desc

        Select '' as Detail,@sTourID as TourID,'' as TourDateRangeID,StartDate,EndDate,AdultPrice,ChildPrice,ID as TourDatesID From TourDates where TourID = @sTourID AND TourDateRangeID is Null

Help me on this query I want to join both into 1 single query ... means want to take 1 data table behind

Answer 1

As far as I understood well from your queries, you needed to take into the result tours that "have" TourDateRange" plus the ones that do not have one.

I hope this would give you the results needed:

Select 
    ISNULL(TourDateRange.Detail, '') AS Detail,                
    TourDates.TourID,
    ISNULL(TourDates.TourDateRangeID, '') AS TourDateRangeID,
    MIN(TourDates.StartDate) AS MinStartDate,
    MAX(TourDates.EndDate) AS MaxEndDate,
    TourDates.AdultPrice,
    TourDates.ChildPrice,
    MAX(TourDates.ID) as MaxTourDatesID
From 
    TourDates 
    LEFT OUTER JOIN TourDateRange
        ON TourDates.TourDateRangeID = TourDateRange.ID
Where 
    TourDates.TourID = @sTourID
GROUP BY
    ISNULL(TourDateRange.Detail, '') ,                
    TourDates.TourID,
    ISNULL(TourDates.TourDateRangeID, '') ,
    TourDates.AdultPrice,
    TourDates.ChildPrice,
Order by
    TourDateRangeID