Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Unanswered: Add column of 2 different tables row wise

    Hi,

    I am just started learning sql and am stuck with a problem

    I have 2 tables:

    select * from test1;

    ID
    ----
    1
    2
    3


    select * from test2;

    ID
    ------
    1
    2
    3


    I want to get the resultset as

    2
    4
    6

    (that is 1+1,2+2,3+3)


    I have tried using join,cartesian product..but not able to get through...

    Looking for a solution

    Thanks,
    Jai

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm afraid you have over-simplified your example beyond comprehension. To respond in kind,
    Code:
    select 2*id from test1
    will give you the desired result set.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    the data is a sample...

    it could be anything...

    suppose its

    table1
    1
    3
    6

    table2:

    2
    5
    7

    resultset
    3 (1+2)
    8 (3+5)
    13 (6+7)


    hope i am clear...

    thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    rows in a table have NO inherent order.

    What happens when tables have different number of rows?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Obviously, there are quite a lot of "what if"s, but - regarding an example @jai0118 has provided, something like this might do the job:
    Code:
    select x.id + y.id
    from (select id, rownum rn from test1 order by id) x,
         (select id, rownum rn from test2 order by id) y
    where x.rn = y.rn;

  6. #6
    Join Date
    Jul 2009
    Posts
    3
    thanks for the solution...

    and if there are unequal number , we are using x.rn=y.rn, so only rows satisfying this condition will be returned..

    Thanks for clearing my doubt...

    Jai

Posting Permissions

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