1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Outer join with column value incremented

Discussion in 'SQL PL/SQL' started by hiswapna, Jun 11, 2010.

  1. hiswapna

    hiswapna Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Table 1
    Split part vechicle count
    1 1 V1 10
    1 2 v2 20
    1 3 v3 30
    2 1 v1 10
    2 2 v2 20
    2 3 v3 30
    3 1 v1 10
    3 2 v2 20
    3 3 v3 30

    Table 2

    Split part vechicle
    1 1 V1 10
    1 2 v2 20
    1 3 v3 30
    2 1 v1 10
    2 2 v4 20--- vehicles changed
    2 3 v5 30 - vehicle changed
    3 1 v1 10
    3 2 v6 20--vehicle changed
    3 3 v3 30

    select *
    from table 1 full outer join table 2 on t1.split=t2.split and t1.part=t2.part and t1.vehicle=t2.vehicle

    i get output as
    split part vechicle t1.coutn t2.count
    1 1 V1 10 10
    1 2 v2 20 20
    1 3 v3 30 30
    2 1 v1 10 10
    2 2 v2 20 null
    2 3 v3 30 null
    2 2 v4 null 20
    2 3 v5 null 30
    3 1 v1 10 10
    3 2 v2 20 20
    3 3 v3 30 null
    3 2 v6 null 30


    but i need output as table 1 records will get new splits number like

    1 1 V1 10 10
    1 2 v2 20 20
    1 3 v3 30 30
    2 1 v1 20 20
    4 2 v2 20 null
    4 3 v3 30 null
    2 2 v4 null 20
    2 3 v5 null 30
    3 1 v1 10 10
    3 2 v2 null 20
    5 2 v6 20 null
    3 3 v3 30 30
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Nice pictures but what, exactly, is the story here? What are 'splits' and, more importantly, what criteria is used to assign numbers to these 'splits'?

    You need to provide answers to those questions before anyone can answer yours.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi swapna,

    provide the table structure, so that everybody can understand very easily.
    You gave the data which resembles metadata.

    So please give structure of tables, and specify your requirement. k