please help: outer join and group function

From: Robert Staedter <staedter_at_mail.boku.ac.at>
Date: 1996/11/29
Message-ID: <57mhfl$93i@mail.boku.ac.at>#1/1

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 Fri Nov 29 1996 - 00:00:00 CST