Re: please help: outer join and group function

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1996/11/29
Message-ID: <329F5E1B.2DC4@teldta.com>#1/1

Robert Staedter wrote:
>
> 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

I think this is what you want. The answer is near the bottom but youbest read it all to make sure I followed your examples and rules:

SQL> select * from v$version;  

BANNER

---

Oracle7 Server Release 7.2.3.0.0 - Production Release PL/SQL Release 2.2.3.0.0 - Production
CORE Version 3.4.3.0.0 - Production
TNS for IBM/AIX RISC System/6000: Version 2.2.3.0.0 - Production NLSRTL Version 3.1.4.6.0 - Production

SQL> desc t1

 Name                            Null?    Type
 ------------------------------- -------- ----
 C1                                       NUMBER
 C2                                       NUMBER

SQL> select * from t1;  

        C1 C2
---------- ----------

         1          2
         1          3
 
SQL> desc t2
 Name                            Null?    Type
 ------------------------------- -------- ----
 S1                                       NUMBER
 S2                                       NUMBER
 

SQL> select * from t2;  

        S1 S2
---------- ----------

         1          0
         2          9

SQL> select t1.c2,t2.s2
  2 from t1, t2
  3 where t2.s1=t1.c1(+);  

        C2 S2
---------- ----------

         2          0
         3          0
                    9
 

SQL> select c1, c2, s1, s2
  2 from (select c1, min(c2) c2 from t1 group by c1), t2   3 where s1=c1(+);  

        C1 C2 S1 S2 ---------- ---------- ---------- ----------

         1          2          1          0
                               2          9
 
SQL> col c1 noprint

SQL> col s1 noprint
SQL> r
  1 select c1, c2, s1, s2
  2 from (select c1, min(c2) c2 from t1 group by c1), t2   3* where s1=c1(+)  

        C2 S2
---------- ----------

         2          0
                    9

Well??????

-- 
                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo

Received on Fri Nov 29 1996 - 00:00:00 CST