Re: How to write SQL statement.

From: Jurij Modic <jmodic_at_src.si>
Date: Mon, 14 Sep 1998 12:41:00 GMT
Message-ID: <35fd0b70.22004577@news.siol.net>

On 14 Sep 1998 09:57:57 GMT, "Aung Aung" <aung<acmarcom_at_singnet.com.sg>> wrote:

>Hello,
>
>I have one problem to retrieve data in one statement. It is as follows:
>Table A. col1 col2 col3
> A X 100
> A Y 200
> B X 100
>
>Table B. A X 10
> A X 20
> A Y 10
> A Y 20
> B X 50
>
>I would like to have the result as
> A 300 60 (A,300 from Table A, 60 from Table B)
> B 100 50 (B,100 from Table A, 50 from Table B)

One of the possible ways would be:

SQL> SELECT x.col1, x.col3_a, y.col3_b

  2  FROM (SELECT col1,SUM(col3) col3_a FROM table_A GROUP BY col1) x,
  3       (SELECT col1,SUM(col3) col3_b FROM table_B GROUP BY col1) y
  4  WHERE x.col1 = y.col1(+);

COL1          COL3_A    COL3_B
---------- --------- ---------
A                300        60
B                100        50

The outer join (y.col1(+) ) takes care of the cases when one group from table_A does not apear in table_B. If one of the groups might apear in any of both tables but not also in the other, then you can use UNION of two aouter joins.

>Thank you,
>
>Aung.

HTH,
--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Sep 14 1998 - 07:41:00 CDT