Difficult SQL Statement

[jw2000]

I am trying to merge these 2 Select statements:

1)
SELECT a.*,
b.flightPilotAssignedTimestamp
FROM Pilot a,
(SELECT pilotid,
MAX(flightPilotAssignedTimestamp) flightPilotAssignedTimestamp
FROM FlightPilot
GROUP BY pilotid) b
WHERE a.pilotid IN (SELECT p.pilotid
FROM Flight f,
Pilot p,
PilotFlightPlaneType pfpt,
PilotFlightLocation pfloc,
PilotFlightType pft
WHERE f.flightPlaneTypeId = pfpt.flightPlaneTypeId
AND f.flightLocationId = pfloc.flightLocationId
AND f.flightTypeId = pft.flightTypeId
AND f.flightId = @flightId
)
AND a.pilotId = b.pilotId
ORDER BY a.pilotScore DESC


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


I am not sure how to to merge these statements as SELECT (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 flightPilotAssignedTimestamp, flight information, and the most recent flightStatusTrackingStatusId from FlightStatusTracking if there is a record or NULL otherwise for each pilot of @flightId.


Tables:

1) FlightStatusTrackingStatus
flightStatusTrackingStatusTrackingId (primary key)
flightStatusTrackingStatusId - to track flight status
flightId
pilotId

flightStatusTrackingStatusId is used to track the flight status, and flightStatusTrackingStatusTrackingId is the primary key of the flightStatusTracking table. These are 2 different fields.

2) Pilot
pilotId (primary key)
pilotName, address, etc

3) Flight
flightId (primary key)
flightName, destination, path, etc

4) FlightPilot - index table to see which flights belong to which pilots
flightId
pilotId

5) PilotFlightPlaneType - index table
pilotId
flightPlaneTypeId

6) PilotFlightLocation - index table
pilotId
flightLocationId

7) PilotFlightType - index table
pilotId
flightTypeId


The merged select should return the most recent flightPilotAssignedTimestamp, flight information, and the most recent flightStatusTrackingStatusId from FlightStatusTracking if there is a record or NULL otherwise for each pilot of @flightId. FlightStatusTrackingStatusId has a value from 1 to 8 (not in any order).

 

[zhavic]

Hi,

I am not sure, but let's say you has this rows in FlightPilot table

FlightPilot table:

flightId   pilotid  flightPilotAssignedTimestamp
   10         12                5
   15         12                21
   14         25                13
   7          12                6
   3          26                19
   65         25                18

And 3 situations:

1. you want informations of last flight ( with biggest timestamp ) for each pilot
e.g. this rows

       flightId   pilotid  flightPilotAssignedTimestamp
          15         12                21
          3          26                19
          65         25                18

2. you want information of each flight for each pilot, but also information of last flight for each pilot
e.g. this result

  flightId pilotid flightPilotAssignedTimestamp last_flightId last_flightPilotAssignedTimestamp  
	10         12                5                    15                   21
    15         12                21                   15                   21
    14         25                13                   65                   18
    7          12                6                    15                   21
    3          26                19                   3                    19
    65         25                18                   65                   18

3. you want some another result, so try to explain it for example this way with sample of datas

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.