SQL Syntax for SELECT within SELECT

[Greg]

Hi, I hope someone can help me here I have 3 very similar queries.
QryLeg1:
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=1));
QryLeg2:
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=2));
QryLeg3:
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=3));

What I need to do is write an SQL statement that selects the lngAirportID
from each query with an outer? join on lngFlightPlanID. I need to define each
query within the final SQL statement. I really have no idea of the correct
syntax to use but I expect it would look something like:

SELECT qryLeg1.lngAirportID, qryLeg2.lngAirportID, qryLeg3.lngAirportID FROM
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=1) AS qryLeg1) etc...

Any help would be greatly appreciated. Thanks!

 

[John Vinson]

Hi, I hope someone can help me here I have 3 very similar queries.
QryLeg1:
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=1));
QryLeg2:
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=2));
QryLeg3:
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=3));

What I need to do is write an SQL statement that selects the lngAirportID
from each query with an outer? join on lngFlightPlanID. I need to define each
query within the final SQL statement. I really have no idea of the correct
syntax to use but I expect it would look something like:

SELECT qryLeg1.lngAirportID, qryLeg2.lngAirportID, qryLeg3.lngAirportID FROM
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=1) AS qryLeg1) etc...

Any help would be greatly appreciated. Thanks!

If what you're trying to ACCOMPLISH ( as distinct from how you are
trying to do it ) is to get a grid with flight plans down the side and
legs across the top, take a look at a Crosstab query. Use the Crosstab
Query Wizard, using lngFlightPlanID as the Row Header and
FlightPlanLeg as the Column Header.

John W. Vinson[MVP]

 

[Smartin]

Hi, I hope someone can help me here I have 3 very similar queries.
QryLeg1:
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=1));
QryLeg2:
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=2));
QryLeg3:
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=3));

What I need to do is write an SQL statement that selects the lngAirportID
from each query with an outer? join on lngFlightPlanID. I need to define each
query within the final SQL statement. I really have no idea of the correct
syntax to use but I expect it would look something like:

SELECT qryLeg1.lngAirportID, qryLeg2.lngAirportID, qryLeg3.lngAirportID FROM
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE (((intLegNumber)=1) AS qryLeg1) etc...

Any help would be greatly appreciated. Thanks!

I don't entirely understand what you are hoping to see. Will the query
below work for you? If not would you post a few lines of sample data and
the expected result?

SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE intLegNumber IN (1,2,3);

 

[Greg]

If what you're trying to ACCOMPLISH ( as distinct from how you are
trying to do it ) is to get a grid with flight plans down the side and
legs across the top, take a look at a Crosstab query. Use the Crosstab
Query Wizard, using lngFlightPlanID as the Row Header and
FlightPlanLeg as the Column Header.

John W. Vinson[MVP]

Hi John, Thanks for the reply. You're correct! A crosstab query will fit the
bill in this instance. However I would like to eventually include additional
column headers, such as ETA and ETD for each leg number. I'm not sure, but I
don't think it can be done in a crosstab query. (What I'm actually attempting
to do is create an SQL statement that I can take to an external VB
application)
Regards, Greg

 

[Greg]

I don't entirely understand what you are hoping to see. Will the query
below work for you? If not would you post a few lines of sample data and
the expected result?

SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg
WHERE intLegNumber IN (1,2,3);

Hi, Perhaps I should explain things a bit better. My table,
AAFP_tbl_FlightPlanLeg has the following fields:
lngFlightPlanID, lngFlightPlanLegID, lngAirportID, dte_ETA, dte_ETD
Sample data may be:
1, 1, 17, (Null), 12:30
1, 2, 28, 13:00, 13:30
1, 3, 17, 14:10, (Null)
2, 1, 12, (Null), 20:50
2, 2, 28, 21:50, (Null)

The end result I am after by using an SQL statement would be to extract:
lngFlightPlanID, Leg1AirportID, Leg1dte_ETA, Leg1dte_ETD, Leg2AirportID,
Leg2dte_ETA, Leg2dte_ETD, Leg3AirportID, Leg3dte_ETA, Leg3dte_ETD.......etc
Sample result for the above data would be:
1, 17, (Null), 12:30, 28, 13:00, 13:30, 17, 14:10, (Null)
2, 12, (Null), 20:50, 28, 21:50, (Null), (Null), (Null), (Null)

Essentially I am attempting to extract denormalized data from a normalized
table. I hope this makes things a little clearer. Thanks, Greg.

 

[John Vinson]

Hi John, Thanks for the reply. You're correct! A crosstab query will fit the
bill in this instance. However I would like to eventually include additional
column headers, such as ETA and ETD for each leg number. I'm not sure, but I
don't think it can be done in a crosstab query. (What I'm actually attempting
to do is create an SQL statement that I can take to an external VB
application)

well, a Crosstab may not be right anyway then: it's not a very
"portable" SQL syntax, AFAIK.

My concern is that I'm guessing that the number of legs will vary -
you'll sometimes need three, sometimes two, maybe sometimes five,
right? Otherwise you could create a Query just joining your queries
one, two and three by Inner Joins. For variable (and unpredictable)
numbers of joins it gets more complex. Am I guessing aright?

John W. Vinson[MVP]

 

[Greg]

well, a Crosstab may not be right anyway then: it's not a very
"portable" SQL syntax, AFAIK.

My concern is that I'm guessing that the number of legs will vary -
you'll sometimes need three, sometimes two, maybe sometimes five,
right? Otherwise you could create a Query just joining your queries
one, two and three by Inner Joins. For variable (and unpredictable)
numbers of joins it gets more complex. Am I guessing aright?

John W. Vinson[MVP]

Yes the number of legs will vary! However, never more than 8 legs. Things
don't really get more unpredictable or complex with the varying number of
legs. If anything, the method you describe, which is the method I currently
use, works extremely well. My problem is this; when I look at the SQL view
for my working method there is reference to QryLeg1,
QryLeg2.....etc......QryLeg8. I want to delete these queries from the
database! So I want to replace any reference to these deleted queries with
their respective SQL statements. So, where theSQL statement once was SELECT
Leg1ETD FROM QryLeg1 I will need to change it to SELECT Leg1ETD FROM
(SELECT{Enter QryLeg1 SQL statement here}........
Do you know what I mean?

 

[Van T. Dinh]

From the way you constructed your Table, I determined that each (valid)
flight plan will have at least 2 records in your Table (which is equivalent
to a physical leg of the flight). In otherwords, a flight plan with n
physical legs) will have (n + 1) records in your Table. For this reason, I
use Inner Join for the first join.

Try something like (I use slightly different name but they should be clear
to you):

SELECT Leg1.FlighPlanID, Leg1.AirportID AS FirstAirPortID,
Leg2.AirportID AS SecondAirtPortID, Leg3.AirportID AS ThirdAirportID
FROM
(
qFlightPlan_Leg1 AS Leg1 INNER JOIN
qFlightPlan_Leg2 AS Leg2 ON Leg1.FlighPlanID = Leg2.FlighPlanID
) LEFT JOIN qFlightPlan_Leg3 AS Leg3 ON Leg2.FlighPlanID =
Leg3.FlighPlanID;

"qFlightPlan_LegX" is equivalent to your set of component Queries.

 

[Greg]

From the way you constructed your Table, I determined that each (valid)
flight plan will have at least 2 records in your Table (which is equivalent
to a physical leg of the flight). In otherwords, a flight plan with n
physical legs) will have (n + 1) records in your Table. For this reason, I
use Inner Join for the first join.

Try something like (I use slightly different name but they should be clear
to you):

SELECT Leg1.FlighPlanID, Leg1.AirportID AS FirstAirPortID,
Leg2.AirportID AS SecondAirtPortID, Leg3.AirportID AS ThirdAirportID
FROM
(
qFlightPlan_Leg1 AS Leg1 INNER JOIN
qFlightPlan_Leg2 AS Leg2 ON Leg1.FlighPlanID = Leg2.FlighPlanID
) LEFT JOIN qFlightPlan_Leg3 AS Leg3 ON Leg2.FlighPlanID =
Leg3.FlighPlanID;

"qFlightPlan_LegX" is equivalent to your set of component Queries.

Thankyou Van for taking the time to offer your contribution. You are correct
in your assumption on the number of records in the table for a valid flight
being >=2. I have substituted your "qFlightPlan_LegX" with my leg queries to
arrive at:

SELECT Leg1.lngFlightPlanID, Leg1.lngAirportID AS FirstAirPortID,
Leg2.lngAirportID AS SecondAirPortID, Leg3.lngAirportID AS ThirdAirportID
FROM (SELECT lngFlightPlanID, lngAirportID FROM AAFP_tbl_FlightPlanLeg WHERE
((intLegNumber)=1) AS Leg1 INNER JOIN SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg WHERE ((intLegNumber)=2) AS Leg2 ON
Leg1.lngFlightPlanID = Leg2.lngFlightPlanID) LEFT JOIN SELECT
lngFlightPlanID, lngAirportID FROM AAFP_tbl_FlightPlanLeg WHERE
((intLegNumber)=3) AS Leg3 ON Leg2.lngFlightPlanID = Leg3.lngFlightPlanID;

However, I get a "syntax error in the from clause" @ the last select keyword
when I try to run the thing. Perhaps there should be a few more parenthesis
or is there something obvious that I have missed? Thanks for your help! Greg

 

[Van T. Dinh]

If you want to combine all into 1 Query / SQL String, try:

========
SELECT Leg1.lngFlightPlanID, Leg1.lngAirportID AS FirstAirPortID,
Leg2.lngAirportID AS SecondAirPortID, Leg3.lngAirportID AS ThirdAirportID
FROM
(
(
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg AS TFL_1
WHERE (intLegNumber=1)
) AS Leg1 INNER JOIN
(
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg AS TFL_2
WHERE (intLegNumber=2)
) AS Leg2 ON Leg1.lngFlightPlanID = Leg2.lngFlightPlanID
)
LEFT JOIN
(
SELECT lngFlightPlanID, lngAirportID
FROM AAFP_tbl_FlightPlanLeg AS TFL_3
WHERE (intLegNumber=3)
) AS Leg3 ON Leg2.lngFlightPlanID = Leg3.lngFlightPlanID;
========