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

    Exclamation Unanswered: Can we relate two tables for two diffrent colum outer join

    table A
    dis_code
    category
    total_amt

    table b
    dist_code
    category
    total_adj_amt


    i want to join these two tables in that manner in which where dis_code ,
    category both r equl then add total_amt+total_adj_amt

    i wrote query

    select a.dis_code ,a.category,sum(a.total_amt+b.total_adj_amt)
    from a,b
    where
    b.dis_code(+)=a.dis_code and
    b.category(+)=a.dis_code and
    group by
    a.dis_code ,a.category


    but result is cartesian product.

    MAY u people have any solution
    Last edited by Ajeet p Singh; 02-22-05 at 05:44.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Does this work?
    Code:
    SELECT A.dis_code, A.category, 
      SUM(DISTINCT A.total_amt) + SUM(DISTINCT B.total_adj_amt) total
    FROM A, B
    WHERE A.dis_code = B.dis_code
    AND A.category = B.category
    GROUP BY A.dis_code, A.category;

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try:
    Code:
    select a.dis_code
    ,      a.category
    ,      a.total_amt + (select sum(b.total_adj_amt)
                          from b
                          where b.dis_code=a.dis_code
                          and b.category=a.category)
    from a;

Posting Permissions

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