Search code examples
sqlfetchinner-joinoffset

How to simplify the below inner JOIN SQL query


I am getting records from different tables and concatenating the values like this so that I can display Flight1, Flight2, Flight3, and Flight4...... as columns. Since I am using the same conditions, Is there any way to simplify this query?

SELECT b.BookingID

,(SELECT TOP 1 CONCAT(il.CCode,il.FlightNumber,';',dc.[Name])
        FROM ILeg il
        INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
             INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
             INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
             INNER JOIN City dc ON dc.CityCode = il.DepartureStation
             INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
             WHERE bp.BookingID =  b.BookingID AND il.CarrierCode = '12T'
             ORDER BY il.STD
             ) AS Flight1

,(SELECT CONCAT((il.CCode,il.FlightNumber,';',dc.[Name]))
        FROM ILeg il
        INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
             INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
             INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
             INNER JOIN City dc ON dc.CityCode = il.DepartureStation
             INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
             WHERE bp.BookingID =  b.BookingID AND il.CarrierCode = '12T'
             ORDER BY il.STD
             OFFSET 1 ROWS
        FETCH NEXT 1 ROWS ONLY) AS Flight2

,(SELECT CONCAT((il.CCode,il.FlightNumber,';',dc.[Name]))
        FROM ILeg il
        INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
             INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
             INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
             INNER JOIN City dc ON dc.CityCode = il.DepartureStation
             INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
             WHERE bp.BookingID =  b.BookingID AND il.CarrierCode = '12T'
             ORDER BY il.STD
             OFFSET 2 ROWS
        FETCH NEXT 1 ROWS ONLY) AS Flight3

FROM Booking b
WHERE b.RecordLocator='1234'

Thank you


Solution

  • You can use a CTE on top and the main query might look as below.

    ;with CTE as 
    (SELECT CONCAT((il.CCode,il.FlightNumber,';',dc.[Name])) as DisplayName
            FROM ILeg il
            INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
                 INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
                 INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
                 INNER JOIN City dc ON dc.CityCode = il.DepartureStation
                 INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
                 WHERE bp.BookingID =  b.BookingID AND il.CarrierCode = '12T'
                 ORDER BY il.STD),
    SELECT b.BookingID
    ,(SELECT TOP 1 * from CTE) AS Flight1
    ,(SELECT TOP 1 * from CTE OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY) AS Flight2
    ,(SELECT TOP 1 * from CTE OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY) AS Flight3
    FROM Booking b
    WHERE b.RecordLocator='1234'