JOIN queries ?

Hi,

Can you give the equivalent query for the following Oracle query :

SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'

In the above query a constant value is used in JOIN, so I am not aware of how to use the JOIN clause for the above constant value ( 'SO' , 'RW' )

Upto my knowledge about JOIN query equivalents :

Oracle query :-

select *from test1 a, test2 b where a.sno(+) = b.col

Equivalent SQL Server query :-

select *from test1 a JOIN test2 b on a.sno = b.col

Hope the above is correct. Here my doubt is, if in the above query, b.col is replaced by a constant value say 'abcd' , then what will be the table name used in right of the JOIN clause. Or any other equivalent for this is available ?

Eg :-

Oracle query :-

select *from test1 a, test2 b where a.sno(+) = 'abcd'

Thanks,
SamHi Sam,

the syntax used by Oracle with "(+)" in the WHERE-clause is equivalent to an OUTER JOIN in SQL Server and DB2.

The example you mentioned should be a LEFT OUTER JOIN, but I'm always a bit confused when using OUTER JOINS in Oracle, so I may be wrong :-)

You can lookup Oracle syntax at http://tahiti.oracle.com
Carsten|||Hi, OK, it is RIGHT JOIN, but my requirement is different, Please go through my posting once again. Thanks for your information.|||SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'

SELECT col2, col3 FROM submission
Right Outer Join lkup so
On so.txt = LTRIM(RTRIM(col2)) AND so.pid = 'SO' AND so.cde = 'RW'