Correct syntax for SELECT statement within a SELECT statement.

[Guest]

I have the following SQL statement which gets it data from 2 queries (QryLeg1
and QryLeg2):
SELECT QryLeg1.Leg1ETD, QryLeg2.Leg2ETD
FROM QryLeg1 LEFT JOIN QryLeg2 ON QryLeg1.lngFlightPlanID =
QryLeg2.lngFlightPlanID;

I would like to remove the queries from the database altogether so I want to
replace the reference to QryLeg1 and QryLeg2 with their respective SQL
statements.

QryLeg1 has the SQL view:
SELECT AAFP_tbl_FlightPlanLeg.lngFlightPlanID, AAFP_tbl_FlightPlanLeg.dteETD
AS Leg1ETD
FROM AAFP_tbl_FlightPlanLeg
WHERE (((AAFP_tbl_FlightPlanLeg.intLegNumber)=1));

QryLeg2 has the SQL view:
SELECT AAFP_tbl_FlightPlanLeg.lngFlightPlanID, AAFP_tbl_FlightPlanLeg.dteETD
AS Leg2ETD
FROM AAFP_tbl_FlightPlanLeg
WHERE (((AAFP_tbl_FlightPlanLeg.intLegNumber)=2));

I would expect the final SQL statement to have a couple of SELECT statements
within another SELECT statement but I am unsure of the correct syntax. Can
you help? Thanks!

 

[Sylvain Lafontaine]

Queries or Views?

If this is for an MDB file then you should ask the question in the newsgroup
m.p.a.queries instead of this one; which is about ADP and SQL-Server. The
syntax of SQL-Server is slightly different from the syntax used in Access
and more important, when you put multiple sub-select statement in Access, it
doesn't take too long before the expression become too complexe for JET.

However, here's your answer for SQL-Server:

SELECT QryLeg1.Leg1ETD, QryLeg2.Leg2ETD
FROM (

SELECT AAFP_tbl_FlightPlanLeg.lngFlightPlanID,
AAFP_tbl_FlightPlanLeg.dteETD
AS Leg1ETD
FROM AAFP_tbl_FlightPlanLeg
WHERE (((AAFP_tbl_FlightPlanLeg.intLegNumber)=1))

) as QryLeg1 LEFT JOIN (

SELECT AAFP_tbl_FlightPlanLeg.lngFlightPlanID,
AAFP_tbl_FlightPlanLeg.dteETD
AS Leg2ETD
FROM AAFP_tbl_FlightPlanLeg
WHERE (((AAFP_tbl_FlightPlanLeg.intLegNumber)=2))

) as QryLeg2 ON QryLeg1.lngFlightPlanID = QryLeg2.lngFlightPlanID

The semi-colons have been removed, the sub-queries put inside parenthesis ()
and given an alias (mandatory).

 

[Guest]

Thankyou! Very helpful.

Queries or Views?

If this is for an MDB file then you should ask the question in the newsgroup
m.p.a.queries instead of this one; which is about ADP and SQL-Server. The
syntax of SQL-Server is slightly different from the syntax used in Access
and more important, when you put multiple sub-select statement in Access, it
doesn't take too long before the expression become too complexe for JET.

However, here's your answer for SQL-Server:

SELECT QryLeg1.Leg1ETD, QryLeg2.Leg2ETD
FROM (

SELECT AAFP_tbl_FlightPlanLeg.lngFlightPlanID,
AAFP_tbl_FlightPlanLeg.dteETD
AS Leg1ETD
FROM AAFP_tbl_FlightPlanLeg
WHERE (((AAFP_tbl_FlightPlanLeg.intLegNumber)=1))

) as QryLeg1 LEFT JOIN (

SELECT AAFP_tbl_FlightPlanLeg.lngFlightPlanID,
AAFP_tbl_FlightPlanLeg.dteETD
AS Leg2ETD
FROM AAFP_tbl_FlightPlanLeg
WHERE (((AAFP_tbl_FlightPlanLeg.intLegNumber)=2))

) as QryLeg2 ON QryLeg1.lngFlightPlanID = QryLeg2.lngFlightPlanID

The semi-colons have been removed, the sub-queries put inside parenthesis ()
and given an alias (mandatory).