Merge 2 Selects Into 1 With Status

[jw2000]

I am trying to merge the 2 Selects:

1)
SELECT b.TotalNoAssignedFlights,
a.*, c.*
FROM Flight a, FlightPilot c,
(SELECT count(distinct(FlightId)) TotalNoAssignedFlights from FlightPilot WHERE PilotId = @PilotId) b
WHERE a.FlightId IN (SELECT distinct(lrl.FlightId) from FlightPilot lrl, Flight lr, Pilot l
WHERE lrl.FlightId = lr.FlightId
AND lrl.PilotId = l.PilotId
AND lrl.PilotId = @PilotId --10260
)
AND a.FlightId = c.FlightId
AND c.Pilotid = @PilotId


2)
select top 1 flightStatusTrackingStatusId, *
from flightStatusTracking
where flightId = 1022
and pilotId = 10260
order by flightStatusTrackingId DESC


I am not sure how to to merge these statements as (2) may not return any records or may return many records, and so I use "TOP" and "order by ... DESC"

The merged select should return flightStatusTrackingStatusId from FlightStatusTracking if there is a record or NULL otherwise.

 

[jw2000]

This looks good except it returns all flightStatusTrackingStatusId's and FlightStatusTrackingStatus information for all records by @PilotId.

The merged select should return the most recent flightStatusTrackingStatusId from FlightStatusTracking if there is a record or NULL otherwise for that specific flight.

 

[jw2000]

This now returns the information for all flights and flightStatusTracking information assigned to a particular Pilot.

How can I return the most recent flightStatusTrackingStatusId if there is a record or NULL otherwise for each flight by @PilotId?



-- @PilotID is input argument

declare @TotalNoAssignedFlights int
SELECT @TotalNoAssignedFlights = COUNT(FlightID) FROM FlightPilot WHERE PilotID=@PilotID

SELECT @TotalNoAssignedFlights AS TotalNoAssignedFlights, a.*, c.*, FS.*
FROM Flight a
INNER JOIN FlightPilot c ON C.FlightId = A.FlightId
LEFT OUTER JOIN
( SELECT FlightID, MAX(flightStatusTrackingStatusId) as lastTrackingID
FROM FlightStatusTrackingStatus
WHERE PilotID = @PilotID
GROUP BY FlightID
) FSMAX ON c.FlightID = FSMAX.FlightID
LEFT OUTER JOIN FlightStatusTrackingStatus FS ON FS.flightStatusTrackingStatusId = FSMAX.lastTrackingID
WHERE c.PilotId = @PilotId
AND FS.FlightId = c.FlightId

 

[jw2000]

This part is working ...

SELECT FlightID, MAX(flightStatusTrackingStatusId) as lastTrackingID
FROM FlightStatusTrackingStatus
WHERE PilotID = @PilotID
GROUP BY FlightID

but the main SELECT (ie. SELECT @TotalNoAssignedFlights AS TotalNoAssignedFlights, a.*, c.*, FS.*) is returning information for all flights and flightStatusTracking information assigned to a particular Pilot.

 

[jw2000]

I can't figure this out. Hope someone can help.

 

[vongrunt]

> How can I return the most recent flightStatusTrackingStatusId if there is a record or NULL otherwise for each flight by @PilotId?

Hm... are you sure code doesn't work? FS and FSMAX should work exactly as you described - one row for each flight by @PilotID, one optional last tracking status for each flight.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)