Access outer joins?

[Christian Meier]

Hello!

I'd like to select an outer join but if I save my query access discards
the outer command?
Do anyone know why and how I can select an outer join on the same
Table??

SELECT a.*, b.F20
FROM BOSS_container AS a LEFT OUTER JOIN BOSS_container AS b ON
(a.F10=b.F10) AND (a.F2=b.F2) AND (a.F1=b.F1)
WHERE a.F5 In ('AQ','VN') And b.F5 Not In ('AQ','VN');

THX
Christian Meier

 

[Guest]

Hi Christian,

Just a hunch on my part, but have you disabled the infamous NameAutocorrupt
option?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[RoyVidar]

Hello!

I'd like to select an outer join but if I save my query access
discards the outer command?
Do anyone know why and how I can select an outer join on the same
Table??

SELECT a.*, b.F20
FROM BOSS_container AS a LEFT OUTER JOIN BOSS_container AS b ON
(a.F10=b.F10) AND (a.F2=b.F2) AND (a.F1=b.F1)
WHERE a.F5 In ('AQ','VN') And b.F5 Not In ('AQ','VN');

THX
Christian Meier

I don't think the keyword "OUTER" is needed. A valid left outer join,
is/can be expressed without the keyword OUTER

.... FROM a LEFT JOIN b on a.SomeField = b.SomeField ...

and should normally also work on the same table.

Observable though, is that Access/Jet doesn't barf if you use the
keyword "OUTER" in the SQL, but removes it when/if you save the query.
The result (on my setup) is the same.

 

[Christian Meier]

THX to all, it was the aouto corrup option!
Regards
Chris

 

[Guest]

Hi Christian,

Glad to see that you got it working now. When you mentioned in your original
post that the query discards the outer command, I made the assumption--which
may be incorrect--that your query also stopped working. Roy is certainly
correct that the keyword OUTER is not required.

If you want to read a related thread, where NameAutocorrupt trashed a
perfectly good SQL statement, read this thread:

http://groups.google.com/group/micr..._frm/thread/52d1339fd6c43e53/168732bc4a31fc23

I've just got in the habit of disabling this *feature* on all new databases.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________