outer join - second post

[Eitan M]

Hello,
I want to do left outer join in access 2002.
1) Isn't any way to do without the words "left outer join", like :
table1.column1 = table2.column2 (+)
that was the simple Oracle syntax, and is there any access syntax.

2) I want to do nested outer join - how ?
Need an example, please.

I have tried something like :
---------------------------------

Select l.FORM_NAME,
f.FORM_DESCR
from table_form_link l
left join maa_form f on l.form_name = f.form_name
left join menu_prog p on f.form_name = p.entry_prog
where l.table_name = 'ACNTS'
order by l.FORM_NAME

but the above doesn't work.
Why ?


Thanks :)

[david epsom dot com dot au]

Re: outer join - second post

> but the above doesn't work.

Because Access/Jet requires braces () around the clauses.
and uses the keyword "as" for table aliases.

Select l.FORM_NAME,
f.FORM_DESCR
from (table_form_lin k AS l
left join maa_form AS f on l.form_name = f.form_name)
left join menu_prog AS p on f.form_name = p.entry_prog
where l.table_name = 'ACNTS'
order by l.FORM_NAME

The nesting of the joins is enforced by the brackets.
That is as close as I can get by eye. If you draw the
query in the Query By Example grid, Access will write
the SQL for you: you can switch to the SQL view to see
valid JET SQL.

(david)
PS: Oracle also supports ANSI join syntax.


"Eitan M" <no_spam_please @nospam_please. com> wrote in message
news:e53q6q$9v2 $1@news2.netvis ion.net.il...[color=blue]
> Hello,
> I want to do left outer join in access 2002.
> 1) Isn't any way to do without the words "left outer join", like :
> table1.column1 = table2.column2 (+)
> that was the simple Oracle syntax, and is there any access syntax.
>
> 2) I want to do nested outer join - how ?
> Need an example, please.
>
> I have tried something like :
> ---------------------------------
>
> Select l.FORM_NAME,
> f.FORM_DESCR
> from table_form_link l
> left join maa_form f on l.form_name = f.form_name
> left join menu_prog p on f.form_name = p.entry_prog
> where l.table_name = 'ACNTS'
> order by l.FORM_NAME
>
> but the above doesn't work.
> Why ?
>
>
> Thanks :)
>
>
>[/color]