newb - mutiple-column join

[dave]

Hi,

very new to SQL queries, and strugling with join concept

I had to do a join based on a single field:

select
*
from
tableA, tableB
where
tableA.value = tableB.value(+)
;

this works fine

but how can i do the same thing while comparing multiple columns ... i
was thinking something like this: (obviously doesn't work)

select
*
from
tableA, tableB
where
[tableA.value1, tableA.value2] = [tableB.value1,
tableB.value2](+)
;

Is there some sort of "tuple" comparison I can do?

Thank you.

[Plamen Ratchev]

What you are using here is the old join syntax, which lists that tables to
join in the FROM clause separated by a comma and then the filters are
applied in the WHERE clause. To use this syntax with multiple columns you
just keep adding the pairs of columns to match, like this:

SELECT <column list>
FROM tableA, tableB
WHERE tableA.col1 = tableB.col1
AND tableA.col2 = tableB.col2
... <and so on keep adding more filters if needed>

The newer syntax uses the JOIN keyword, like this:

SELECT <column list>
FROM tableA
INNER JOIN tableB
ON tableA.col1 = tableB.col1
AND tableA.col2 = tableB.col2
... <and so on keep adding more filters if needed>

There are different types of joins: CROSS, INNER, and OUTER (LEFT, RIGHT, or
FULL). Also, there are a couple ANSI joins currently not supported by SQL
Server (natural and union joins).

Here is more information on using joins:
http://msdn2.microsoft.com/en-us/library/ms191472.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

....and just noted the (+), seems you are using the old Oracle outer join
syntax, which is equivalent to LEFT OUTER JOIN. I believe if you have a
version of Oracle higher than Oracle 8i you can use the new syntax.

Plamen Ratchev
http://www.SQLStudio.com