Re: OUTTER JOINS

In a previous article, doug.hewko_at_synapse.org (Doug Hewko) says:

>
>How would someone create an outter join, between two tables, that
>involves three columns? The two tables require three keys to join. The
>outter join feature (+) is limited to one column. There may or may not
>be a relationship between the two tables.
>
>For example, I want to do:
> SELECT a.col1, a.col2, b.col3
> FROM table_a a, table_b b
> WHERE a.table_name = UPPER('&user_input_value')
> AND a.table_name = b.table_name (+)
> AND a.owner_name = b.owner_name (+)
> AND a.column_name = b.column_name (+)
> ORDER by a.column_name ASC;
>
>..but unfortunately ORACLE 6 does not accept multiple '(+)'s even
>though they all relate to ONE table.
>

How about

	select a.col1, a.col2, b.col3
	from table_a a, table_b b
	where a.table_name = upper('&user_input_value')
	and a.table_name = b.table_name (+)
	and a.owner_name = nvl(b.owner_name, a.owner_name)
	and a.column_name = nvl(b.column_name, a.column_name)
	order by a.column_name asc;

I haven't actually tried this before posting, but I have done similar things. Try it.

-- 
Diana Tracy, System Designer		-- Excitement, Adventure
bs794_at_cleveland.Freenet.Edu		-- and Really Wild Things