Left Join Nested Query

  • Thread starter michael.thompson
  • Start date
M

michael.thompson

How do I convert a nested query from an inner join to a left outer
join.

Below is the syntax:
'FROM SQL STATEMENT
strSQL2 = "SELECT T1.IMLITM, T2.PDDOCO, T2.PDDCTO, T2.PDLITM,
T2.PDDSC1, T2.ORDERED, T2.RECTTOTAL, T2.PDVR01, T3.SDDOCO, T3.SDDCTO,
T3.SOLD, T3.SHIPTOTAL "
strSQL2 = strSQL2 & "FROM [SELECT DISTINCT IMLITM FROM
PRODDTA_F41021]. AS T1, "
strSQL2 = strSQL2 & "[SELECT PDDOCO, PDDCTO, PDLITM, PDDSC1,
Sum(PDUORG) AS ORDERED, Sum(PDUREC) AS RECTTOTAL, PDVR01 FROM
PRODDTA_F4311 "
strSQL2 = strSQL2 & "GROUP BY PDDOCO, PDDCTO, PDLITM, PDDSC1,
PDVR01]. AS T2, "
strSQL2 = strSQL2 & "[SELECT SDDOCO, SDDCTO, SDLITM, Sum(SDUORG) AS
SOLD, Sum(SDSOQS) AS SHIPTOTAL FROM PRODDTA_F4211 "
strSQL2 = strSQL2 & "GROUP BY SDDOCO, SDDCTO, SDLITM]. AS T3 "
strSQL2 = strSQL2 & "WHERE (((T1.IMLITM) = [T2].[PDLITM] And
(T1.IMLITM) = [T3].[SDLITM] And ([T2].[PDVR01]) = [T3.SDDOCO] And
(T1.IMLITM) = '" & strItem & "')) "
strSQL2 = strSQL2 & "ORDER BY T1.IMLITM;"
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In versions of SQL that could handle it there were special comparison
operators that would indicate LEFT/RIGHT JOINs (*= or =*). Access SQL
doesn't recognize those operators, so you'll have to use the words LEFT
JOIN or RIGHT JOIN between the table names.

You can take the SQL string you've created in VBA & put it into a
QueryDef's SQL view; then switch over to the QBE design view & link the
tables as you desire. Read the Access Help article on designing
queries.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQdsgAIechKqOuFEgEQLfxQCfbpDApNB5HlISvdhGe4dWZT+uG4UAnirf
s0dcpp9bPYclJ4173XHOK393
=clh1
-----END PGP SIGNATURE-----
 
?

*

Sometimes you also have to break it into several queries when using Left
Outer Joins in conjuncion with Inner or Cross joins.


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In versions of SQL that could handle it there were special comparison
operators that would indicate LEFT/RIGHT JOINs (*= or =*). Access SQL
doesn't recognize those operators, so you'll have to use the words LEFT
JOIN or RIGHT JOIN between the table names.

You can take the SQL string you've created in VBA & put it into a
QueryDef's SQL view; then switch over to the QBE design view & link the
tables as you desire. Read the Access Help article on designing
queries.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQdsgAIechKqOuFEgEQLfxQCfbpDApNB5HlISvdhGe4dWZT+uG4UAnirf
s0dcpp9bPYclJ4173XHOK393
=clh1
-----END PGP SIGNATURE-----


How do I convert a nested query from an inner join to a left outer
join.

Below is the syntax:
'FROM SQL STATEMENT
strSQL2 = "SELECT T1.IMLITM, T2.PDDOCO, T2.PDDCTO, T2.PDLITM,
T2.PDDSC1, T2.ORDERED, T2.RECTTOTAL, T2.PDVR01, T3.SDDOCO, T3.SDDCTO,
T3.SOLD, T3.SHIPTOTAL "
strSQL2 = strSQL2 & "FROM [SELECT DISTINCT IMLITM FROM
PRODDTA_F41021]. AS T1, "
strSQL2 = strSQL2 & "[SELECT PDDOCO, PDDCTO, PDLITM, PDDSC1,
Sum(PDUORG) AS ORDERED, Sum(PDUREC) AS RECTTOTAL, PDVR01 FROM
PRODDTA_F4311 "
strSQL2 = strSQL2 & "GROUP BY PDDOCO, PDDCTO, PDLITM, PDDSC1,
PDVR01]. AS T2, "
strSQL2 = strSQL2 & "[SELECT SDDOCO, SDDCTO, SDLITM, Sum(SDUORG) AS
SOLD, Sum(SDSOQS) AS SHIPTOTAL FROM PRODDTA_F4211 "
strSQL2 = strSQL2 & "GROUP BY SDDOCO, SDDCTO, SDLITM]. AS T3 "
strSQL2 = strSQL2 & "WHERE (((T1.IMLITM) = [T2].[PDLITM] And
(T1.IMLITM) = [T3].[SDLITM] And ([T2].[PDVR01]) = [T3.SDDOCO] And
(T1.IMLITM) = '" & strItem & "')) "
strSQL2 = strSQL2 & "ORDER BY T1.IMLITM;"
Click to expand...
Click to expand...
 
Top