left outer join

[Eitan M]

Hello,

I want to do select like this :
select t1.col_2
from table_1 t1, table_2 t2
where t1.col_1 = t2.col_1 (+)

The above is correct syntax for Oracle.
What is the correct syntax for access.
(Not the syntax : left outer join table_1 on table_2 etc... , but something
like (+), BTW - what is the table that (+) stands besides it).

Thanks :)

[Tim Marshall]

Re: left outer join

Eitan M wrote:

Hello,
>
I want to do select like this :
select t1.col_2
from table_1 t1, table_2 t2
where t1.col_1 = t2.col_1 (+)
>
The above is correct syntax for Oracle.
What is the correct syntax for access.

The above is referred to as an "outer join" in Oracle.

In Access, or more properly, "Jet" (MS Jet is the database engine that
comes with MS Access) you set up linked tables in your Access mdb. By
default, linked tables in the mdb are prefixed by the schema name and an
undescore, ie, the tables would be something like DB_T1 and DB_T2.

I'll use T1 and T2, below.

In Access/Jet, the way to accomplish an Oracle outer join is to use a
left join (you can also use right joins, but left joins are encouraged)
and it would be like this:

SELECT
t1.col_2
FROM
t1 left join t2 on t1.col_1 = t2.col_1

Notice that the join is effected in the FROM clause and not in the where
clause as per Oracle.

I highly recommend using the very useful query builder in MS Access if
you are going to be using Oracle linked tables. It will help you get
the syntax of such joins correct which is very useful when you have lots
of tables in a select statement.

Using linked tables in MS Access is very useful with respect to the
ability to write to such tables. However, you can use "Oraclese" if
you're more comfortable with it by using "pass through" queries.



--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me