Re: please help: outer join and group function

The following select would solve your problem

 select min(decode(t1.c2,nvl,0,t1,c2) ) A1, t2.s2 A2  from t1, t2
 where t2.s1=t1.c1(+)
group by A2
 which gives output:

 A1	A2
2	0
0	9 ( This the row where c2 has a null value but the decode function would
convert it
	     to 0 .If the zero is acceptable then this solution should be ok)

Note : The decode function would convert the value with null value to zero. Regards
Sanjay T Mathew
Oracle Database Administrator (mastek_at_burton.co.uk) Innovations PLC
U.K

Robert Staedter <staedter_at_mail.boku.ac.at> wrote in article <57mhfl$93i_at_mail.boku.ac.at>...
> Hello,
>
> I would appreciate any help on the following problem:
> I have two tables, for simplicity let's call them T1 and T2, of the
 following form:
>
> T1:
> c1 c2
> -------
> 1 2
> 1 3
>
> T2:
> s1 s2
> -------
> 1 0
> 2 9
>
> I want to outer join these tables using
>
> select t1.c2,t2.s2
> from t1, t2
> where t2.s1=t1.c1(+);
> which gives:
> C2 S2
> --------- ---------
> 2 0
> 3 0
> 9
>
> However, I additionally want, for each value of s2, only the row with the
 minimum value of c2
> *AND* I need the result rows of the outer join where c2 is NULL:
>
> C2 S2
> --------- ---------
> 2 0
> 9
>
> Applying "min(c2) ... group by s2" of course results in losing the rows
 where c2 is null.
> As to the complexity of the real joins (these are only demonstration
 tables), a UNION
> approach is useless.
>
> Does anyone have a solution?
> Thanks in advance,
>
> -- Robert Staedter
> University for Agriculture
> Vienna, Austria
>
>
Received on Sat Nov 30 1996 - 00:00:00 CST