Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    1

    Unanswered: inner join problem

    I got 2 tables.

    Table1 got columns ID and amt
    Table2 got column ID

    how to I create a join that I only get the sum of the amt in table1 for granted that the IDs in Table1 is = to the IDs in Table2.

    Example:
    Table 1
    ID Amt
    S123455 10.00
    W123666 20.00
    Q123454 30.00

    Table 2
    ID

    S123455
    Q123454

    results I want to get is sum(amt)=40
    *because (S123455)10.00+(Q123454)30.00 = 40.00
    *the sum do not include ID W123666 because its ID is not in Table 2.

    I hope someone there will be able to help me. I am using oracle 8i which do not have the function "inner join" and that is why I am stuck.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Before ANSI came up with "INNER JOIN" we all used to get by with "="

    select sum(table1.amt)
    from table1, table2
    where table1.id = table2.id;

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    select sum(t1.amt)
    from tab1 t1
    where t1.id in (select t2.id from tab2 t2);

    should also work.
    Last edited by Littlefoot; 08-27-04 at 07:14.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •