This discussion is archived
4 Replies Latest reply: Jan 8, 2013 7:54 AM by 931250 RSS

Outer join on same table

931250 Newbie
Currently Being Moderated
Hello:

I have tried to join same table as below but it didn't work. I just need the sum of column 'value' from table tab1 for the trans_types O and S. And I need to add the value of trans type E and M only if it exists for the same ID and SITE.

select sum(a.value + b.value)
from tab1 a, tab1 b
where a.id = b.id(+)
and a.site = b.site(+)
and a.trans_type in ('O','S')
and b.trans_type in ("E','M')
and a.id = 1;

It works fine if I use a subquery in place of table B as below

select sum(a.value + b.value)
from tab1 a, (select value from tab1 where id = 1 and trans_type in ('E','M') ) b
where a.id = b.id(+)
and a.site = b.site(+)
and a.trans_type in ('O','S')
and a.id = 1;

Is it the correct way to fix this or is there any other option to fix the first query?

Thanks,
Rob
  • 1. Re: Outer join on same table
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Rob,
    Robsto wrote:
    Hello:

    I have tried to join same table as below but it didn't work. I just need the sum of column 'value' from table tab1 for the trans_types O and S. And I need to add the value of trans type E and M only if it exists for the same ID and SITE.

    select sum(a.value + b.value)
    from tab1 a, tab1 b
    where a.id = b.id(+)
    and a.site = b.site(+)
    and a.trans_type in ('O','S')
    and b.trans_type in ("E','M')
    You need a single-quote before E, not a double-quote.
    and a.id = 1;

    It works fine if I use a subquery in place of table B as below

    select sum(a.value + b.value)
    from tab1 a, (select value from tab1 where id = 1 and trans_type in ('E','M') ) b
    where a.id = b.id(+)
    and a.site = b.site(+)
    and a.trans_type in ('O','S')
    and a.id = 1;

    Is it the correct way to fix this or is there any other option to fix the first query?

    Thanks,
    Rob
    Here's one way:
    {code}
    SELECT     NVL ( SUM (a.value + b.value)
         , 0
         )          AS total_value
    FROM     tab1 a
    ,     tab1     b
    WHERE     a.id          = 1
    AND     a.id          = b.id
    AND     a.site     = b.site
    AND     a.trans_type     IN ('O', 'S')
    AND     a.trans_type     IN ('E', 'M')
    ;
    {code}
    Notice that this query uses an inner join, but both of the queries you posted used outer joins. That's beacuse the only thing being SELECTed is
    {code}
    SUM (a.value + b.value)
    {code}
    If a row from a has no matching row in b, then b.value will be NULL, and therefore
    {code}
    a.value + b.value
    {code}
    will be NULL, regardless of what a.value is. Since rows where a has no match in b are not contributing to the total, you might as well use a more efficient inner join.

    It could be that neither of the queries you posted are returning the correct total in all situations, and that you really do need an outer join. If so, post some sample data (CREATE TABLE and INSERT statements) such that the query I posted does not produce what you want, and post and the correct results you want from that data.
    See the forum FAQ {message:id=9360002}

    Whatever you want, you can probably do it without a sub-query, either by using ANSI join syntax (which I recommend for all joins, especially outer joins), or perhaps simply by including a + sign in the join condition
    {code}
    AND      b.trans_type (+) IN ('E', 'M')
    {code}
    Then again, depending on your data and your requirements, you might not need any join at all. You might be able to get the results you want with one pass through the table, using a more complicated WHERE clause.

    Edited by: Frank Kulash on Jan 7, 2013 4:02 PM
  • 2. Re: Outer join on same table
    931250 Newbie
    Currently Being Moderated
    Thanks for reply. Please see the results from my SQLs, second set of SQLs giving me correct answer. First set of queries do not return any value if there is no record with trans_type 'E' or 'M'.

    SQL> create table tab1
    2 (
    3 id number(5),
    4 site varchar2(5),
    5 trans_type char(1),
    6 value number(10,2)
    7 );

    Table created.

    SQL> --inserting records
    SQL>
    SQL> Insert into tab1 VALUES (1,'TEST','O',10000.00);

    1 row created.

    SQL>
    SQL> Insert into tab1 VALUES (1,'TEST','M',-10000.00);

    1 row created.

    SQL>
    SQL> Insert into tab1 VALUES (2,'TEST','O',20000.00);

    1 row created.

    SQL>
    SQL> Insert into tab1 VALUES (3,'TEST','O',20000.00);

    1 row created.

    SQL>
    SQL> Insert into tab1 VALUES (3,'TEST','M',-15000.00);

    1 row created.

    SQL>
    SQL> commit ;

    Commit complete.

    SQL> Select sum(nvl(a.value,0) + nvl(b.value,0))
    2 from tab1 a, tab1 b
    3 where a.id = b.id
    4 and a.site = b.site
    5 and a.trans_type in( 'O','S')
    6 and b.trans_type in( 'E', 'M')
    7 and a.id = 1;

    SUM(NVL(A.VALUE,0)+NVL(B.VALUE,0))
    ----------------------------------
    0

    SQL> Select sum(nvl(a.value,0) + nvl(b.value,0))
    2 from tab1 a, tab1 b
    3 where a.id = b.id
    4 and a.site = b.site
    5 and a.trans_type in( 'O','S')
    6 and b.trans_type in( 'E', 'M')
    7 and a.id = 2;

    SUM(NVL(A.VALUE,0)+NVL(B.VALUE,0))
    ----------------------------------


    SQL> Select sum(nvl(a.value,0) + nvl(b.value,0))
    2 from tab1 a, tab1 b
    3 where a.id = b.id
    4 and a.site = b.site
    5 and a.trans_type in( 'O','S')
    6 and b.trans_type in( 'E', 'M')
    7 and a.id = 3;

    SUM(NVL(A.VALUE,0)+NVL(B.VALUE,0))
    ----------------------------------
    5000


    SQL> select sum(nvl(a.value,0) + nvl(b.value,0))
    2 from tab1 a, (select id, site, value from tab1 where tab1.trans_type in ('E','M')) b
    3 where a.id = b.id(+)
    4 and a.site=b.site(+)
    5 and a.trans_type in ('O','S')
    6 and a.id = 1;

    SUM(NVL(A.VALUE,0)+NVL(B.VALUE,0))
    ----------------------------------
    0

    SQL> select sum(nvl(a.value,0) + nvl(b.value,0))
    2 from tab1 a, (select id, site, value from tab1 where tab1.trans_type in ('E','M')) b
    3 where a.id = b.id(+)
    4 and a.site=b.site(+)
    5 and a.trans_type in ('O','S')
    6 and a.id = 2;

    SUM(NVL(A.VALUE,0)+NVL(B.VALUE,0))
    ----------------------------------
    20000

    SQL> select sum(nvl(a.value,0) + nvl(b.value,0))
    2 from tab1 a, (select id, site, value from tab1 where tab1.trans_type in ('E','M')) b
    3 where a.id = b.id(+)
    4 and a.site=b.site(+)
    5 and a.trans_type in ('O','S')
    6 and a.id = 3;

    SUM(NVL(A.VALUE,0)+NVL(B.VALUE,0))
    ----------------------------------
    5000
  • 3. Re: Outer join on same table
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Robsto wrote:
    Thanks for reply. Please see the results from my SQLs, second set of SQLs giving me correct answer. First set of queries do not return any value if there is no record with trans_type 'E' or 'M'.
    I am so confused.

    Is each set of queries the same except for the hard-coded value of a.id? If so, it will be a lot easier to test if we just GROUP BY a.id, and not restrict the query to any single value. You can un-comment the condition "a.id = x" when testing is finsihed, if you want to.

    If I understand you, the query which produces the right results is:
    SQL> select sum(nvl(a.value,0) + nvl(b.value,0))
    2 from tab1 a, (select id, site, value from tab1 where tab1.trans_type in ('E','M')) b
    3 where a.id = b.id(+)
    4 and a.site=b.site(+)
    5 and a.trans_type in ('O','S')
    6 and a.id = 3;
    The query below is more efficient, and I find it easier to read and understand as well:
    SELECT    a.id
    ,       NVL (SUM (a.value), 0)
         + NVL (SUM (b.value), 0)     AS total_value
    FROM              tab1      a
    LEFT OUTER JOIN      tab1     b  ON   b.id          = a.id
                               AND  b.site          = a.site
                      AND     b.trans_type     IN ('E', 'M')
    WHERE   a.trans_type IN ('O', 'S')
    -- AND       a.id            = 3
    GROUP BY  a.id
    ORDER BY  a.id
    ;
    Output:
    `       ID TOTAL_VALUE
    ---------- -----------
             1           0
             2       20000
             3        5000
    The reason why it is more efficient is that it is doing the SUM before calling NVL, so it only has to call NVL 2 times, regardless of how many rows are in the table. The query you posted calls NVL 2 times per row.
    Sometimes, ANSI outer joins are more efficient than the equivalent old style outer joins, and they are never slower. However, most of the time they are equally efficient, and this is probably one of those cases.

    If you're not familiar with ANSI join syntax, you may not find this easier to read and understand, at least not yet. I suggest you get used to it, however. There will be times when the ANSI join syntax is patently clearer and more efficient; it would be silly not to use ANSI syntax in those cases, and, given that you'll have to learn to read and understand ANSI syntax for those cases, you might as well use it in all cases.
  • 4. Re: Outer join on same table
    931250 Newbie
    Currently Being Moderated
    Thanks Frank. Your new SQL is what exactly I was looking for.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points