p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

activex_data_objects thread: Left Outer Joins


Message #1 by "Steve Wark" <ccontras@b...> on Tue, 3 Jul 2001 05:05:16
If you have base table containing data plus key fields for a series of

related link tables, and you want to return a record set which contains all

the records from the base table, and depending on if the key fields match,

data from the link tables you can use the LEFT OUTER JOIN command which

works fine for a single link table ie



Base Table AAAA

Link Table BBBB



SELECT *

FROM AAAA LEFT OUTER JOIN BBBB ON AAAA.key=BBBB.key



But as soon as I increase this to say two (2) links tables it fails when

using MS Access database from inside ADO Control.



Base Table AAAA

Link Table BBBB

Link Table CCCC



SELECT *

FROM AAAA LEFT OUTER JOIN BBBB ON AAAA.key=BBBB.key

LEFT OUTER JOIN CCCC ON AAAA.key=CCCC.key



Can anyone advise how the above can be solved for tables in an MS Access

(Office 97) database using the Jet 4.0 Provider.



Regards

Steve Wark





Message #2 by "Wally Burfine" <oopconsultant@h...> on Tue, 03 Jul 2001 15:04:30 -0000
Try putting the Parens in:



FROM (AAAA LEFT JOIN BBBB ON AAAA.Key=BBBB.Key)

LEFT JOIN CCCC ON AAAA.Key = CCCC.Key;



Wally



>From: "Steve Wark" <ccontras@b...>

>Reply-To: "ActiveX_Data_Objects" <activex_data_objects@p...>

>To: "ActiveX_Data_Objects" <activex_data_objects@p...>

>Subject: [activex_data_objects] Left Outer Joins

>Date: Tue, 3 Jul 2001 05:05:16

>

>If you have base table containing data plus key fields for a series of

>related link tables, and you want to return a record set which contains all

>the records from the base table, and depending on if the key fields match,

>data from the link tables you can use the LEFT OUTER JOIN command which

>works fine for a single link table ie

>

>Base Table AAAA

>Link Table BBBB

>

>SELECT *

>FROM AAAA LEFT OUTER JOIN BBBB ON AAAA.key=BBBB.key

>

>But as soon as I increase this to say two (2) links tables it fails when

>using MS Access database from inside ADO Control.

>

>Base Table AAAA

>Link Table BBBB

>Link Table CCCC

>

>SELECT *

>FROM AAAA LEFT OUTER JOIN BBBB ON AAAA.key=BBBB.key

>LEFT OUTER JOIN CCCC ON AAAA.key=CCCC.key

>

>Can anyone advise how the above can be solved for tables in an MS Access

>(Office 97) database using the Jet 4.0 Provider.

>

>Regards

>Steve Wark

>

>




  Return to Index