Correct syntax for SELECT statement within a SELECT statement.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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).
 
Thankyou! Very helpful.

Sylvain Lafontaine said:
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).
Click to expand...
 
Back
Top