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.

 

[travisbrown]

I don't really know what you mean by merge. i'm guessing you want an outer join, but it's hard to figure out. You need to explain the tables and keys and maybe should show what you expect the resulting data to look like.

 

[jw2000]

Tables:

1) FlightStatusTrackingStatus
flightStatusTrackingStatusId (primary key)
flightId
pilotId

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


I would like to return all TotalNoAssignedFlights, Flight and FlightPilot information as well as the flightStatusTrackingStatusId for each flight by @PilotId.

 

[jw2000]

The merged select should return the "lastest" flightStatusTrackingStatusId (that is why I am using TOP and ORDER BY ... DESC in the second SELECT) from FlightStatusTracking if there is a record or NULL otherwise.

The second select may not return any records or may return many records.

 

[jw2000]

Any suggestions?

 

[vongrunt]

Derived tables and left outer join.

------
"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)

 

[jw2000]

How would I use derived tables and outer join here?

 

[vongrunt]

Let's cleanup 1st query first... it looks a bit so I'd insist on that before merging.

SELECT   b.TotalNoAssignedFlights,  
 a.*, c.*
FROM     Flight a, FlightPilot c,  
        (SELECT count(distinct(FlightId)) TotalNoAssignedFlights from FlightPilot WHERE PilotId = @PilotId) b

If (flightId, pilotId) are unique in table FlightPilot (e.g. no pilot can be assigned to same flight twice) then DISTINCT is not necessary.

WHERE    a.FlightId IN (SELECT distinct(lrl.FlightId) from FlightPilot lrl, Flight lr, Pilot l

Same here... if you are using IN(), duplicates don't matter.

WHERE lrl.FlightId = lr.FlightId
AND lrl.PilotId = l.PilotId
AND lrl.PilotId = @PilotId --10260
     )
AND a.FlightId = c.FlightId
AND c.Pilotid = @PilotId

What is the purpose of table l (Pilot) here? It is nowhere used... except for dummy inner join.

Also: do you want to run final query for single pilot only (@Pilot) or many pilots?

------
"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)

 

[jw2000]

The purpose of Pilot here is that I would like to return pilot information (*). The final query should run for one Pilot (ie. by @pilotId).

 

[vongrunt]

> The purpose of Pilot here is that I would like to return pilot information (*).

Sorry, * is used only on tables a (Flight) and c (FlightPilot). Table Pilot is used only in WHERE clause, not SELECT.

Can you briefly explain what is 1st query supposed to return? I guess "gimme all flights for specified @pilot"... but some pieces look redundant.

------
"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)

 

[vongrunt]

One additional question: in 2nd query there is column named flightStatusTrackingId. Is this a typo (perhaps should be flightStatusTrackingStatusId) or not?

------
"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)

 

[jw2000]

flightStatusTrackingId is correct.

The 1st query should return all flights for a specified @pilotId.

 

[jw2000]

The 1st query should also return TotalNoAssignedFlights for the pilot.

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

 

[jw2000]

Can anyone help?

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(fp.FlightId) from FlightPilot fp, Flight f, Pilot p
WHERE fp.FlightId = f.FlightId
AND fp.PilotId = p.PilotId
AND fp.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


Tables:

1) FlightStatusTrackingStatus
flightStatusTrackingStatusId (primary key)
flightId
pilotId

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


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 flightStatusTrackingStatusId from FlightStatusTracking if there is a record or NULL otherwise.

 

[jw2000]

Can anyone help? I can't figure this out.

 

[vongrunt]

I made complete query but one thing still bugs me a lot. You mentioned this structure for table FlightStatusTrackingStatus

flightStatusTrackingStatusId (primary key)
flightId
pilotId

And 2nd query is:

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

Where column flightStatusTrackingId comes from?

------
"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)

 

[jw2000]

There isn't a field for flightStatusTrackingId.

 

[vongrunt]

Argh... ... (no offense intended). This means flightStatusTrackingId is a typo (see one of my previous questions above)?

------
"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)

 

[jw2000]

You are right ... I apologize. Any suggestions on how I can get this to work?

 

[vongrunt]

Try this:

-- @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

This should give you all flights for specified @PilotID together with number of assigned flights (constant in query?) and last tracking row for each flight.

Btw. I had no sample data to test this monster so... expect bugs & features

------
"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)