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!

Joining two tables

Discussion in 'SQL PL/SQL' started by amarbose, May 8, 2011.

  1. amarbose

    amarbose Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    have provided the data so it is easy to write the sql query:

    please provide me the query to dereive the column E in the output(I want a sql query to get the column in output)

    Code (Text):
    with table_1 as(
    SELECT 200 A, 10 B, 345 C, 1 D from dual UNION ALL
    SELECT 200, 10, 346, 2 from dual UNION ALL
    SELECT 200, 10, 347, 2 from dual UNION ALL
    SELECT 200, 10, 348, 2 from dual UNION ALL
    SELECT 200, 10, 349, 3 from dual UNION ALL
    SELECT 201, 11, 350, 1 from dual UNION ALL
    SELECT 203, 12, 351, 2 from dual UNION ALL
    SELECT 341, 34, 352, 3 from dual UNION ALL
    SELECT 456, 65, 353, 2 from dual UNION ALL
    SELECT 456, 65, 354, 2 from dual UNION ALL
    SELECT 456, 65, 355, 2 from dual UNION ALL
    SELECT 677, 53,356, 2 from dual UNION ALL
    SELECT 677, 53, 357, 3 from dual UNION ALL
    SELECT 341, 27, 358, 3 from dual ),
    table_2 as (
    SELECT 203 A1, 12 B1, 233 C1 from dual UNION ALL
    SELECT 341, 34, 456 from dual UNION ALL
    SELECT 456, 65, 343 from dual UNION ALL
    SELECT 597,91,901 from dual)
    Logic to populate the column E is

    For a set value for the column B and A the population logic for column E is provided below:
    1)if value 1 is provided in the D column of table_1 then populate E column with value from C column (where value of D column=1) for the entire set.
    2) if value 1 is not provided in the D column for a particular set of values from column A and B for the table_1 for a set 1 but matching records is present in table_2 then populate column E with value from Table_2.C1
    3) if value 1 is not provided in the D column for a particular set of values from column A and B for the table_1 and not matching value is present in table_2 then populate the E column with 0.

    In the two table the join condition is:
    table_1.B=table_2.B1
    table_1.A=table_2.A1



    Out put is:
    [TABLE]A B C D E
    200 10 345 1 345
    200 10 346 2 345
    200 10 347 2 345
    200 10 348 2 345
    200 10 349 3 345
    201 11 350 1 350
    203 12 351 2 233
    341 34 352 3 456
    456 65 353 2 343
    456 65 354 2 343
    456 65 355 2 343
    677 53 356 2 0
    677 53 357 3 0
    341 27 358 3 0[/TABLE]
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What have you written to solve this problem? Post your attempt so someone can assist you.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Still it is not clear. How do u get the value '345' for the rows 2,3,4 and 5 in your output ?
     
  4. ylongbeach

    ylongbeach Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    here is my answer, anyone come out any better ideal?
    and still "How do u get the value '345' for the rows 2,3,4 and 5 in your output ?"

    -----------------------
    select -- condition 1
    a,b,c,d,c e
    from
    table_1
    where
    d=1
    union all -- condition 2
    select
    a,b,c,d,c1 e
    from
    table_1,
    table_2
    where
    table_1.d > 1 and
    table_1.a = table_2.a1 and
    table_1.b = table_2.b1
    union all -- condition 3
    select
    a,b,c,d, 0 e
    from
    table_1.d > 1 and
    table_1.a <> table_2.a1 and
    table_1.b <> table_2.b1
    group by
    a,b,c,d
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You don't get 345 for lines 2-5 without using lag(); as specified by the original post the query I have is:

    Code (SQL):
     
    SQL> WITH table_1 AS(
    2 SELECT 200 A, 10 B, 345 C, 1 D FROM dual UNION ALL
    3 SELECT 200, 10, 346, 2 FROM dual UNION ALL
    4 SELECT 200, 10, 347, 2 FROM dual UNION ALL
    5 SELECT 200, 10, 348, 2 FROM dual UNION ALL
    6 SELECT 200, 10, 349, 3 FROM dual UNION ALL
    7 SELECT 201, 11, 350, 1 FROM dual UNION ALL
    8 SELECT 203, 12, 351, 2 FROM dual UNION ALL
    9 SELECT 341, 34, 352, 3 FROM dual UNION ALL
    10 SELECT 456, 65, 353, 2 FROM dual UNION ALL
    11 SELECT 456, 65, 354, 2 FROM dual UNION ALL
    12 SELECT 456, 65, 355, 2 FROM dual UNION ALL
    13 SELECT 677, 53,356, 2 FROM dual UNION ALL
    14 SELECT 677, 53, 357, 3 FROM dual UNION ALL
    15 SELECT 341, 27, 358, 3 FROM dual ),
    16 table_2 AS (
    17 SELECT 203 A1, 12 B1, 233 C1 FROM dual UNION ALL
    18 SELECT 341, 34, 456 FROM dual UNION ALL
    19 SELECT 456, 65, 343 FROM dual UNION ALL
    20 SELECT 597,91,901 FROM dual)
    21 SELECT a, b, c, d,
    22 CASE WHEN d=1 THEN c
    23 WHEN d<>1 AND a=a1 AND b=b1 THEN c1
    24 ELSE 0 END e
    25 FROM table_1 LEFT OUTER JOIN table_2
    26 ON (table_1.B=table_2.B1 AND table_1.A=table_2.A1)
    27 ORDER BY 1,2,3
    28 /
     
             A          B          C          D          E
    ---------- ---------- ---------- ---------- ----------
           200         10        345          1        345
           200         10        346          2          0
           200         10        347          2          0
           200         10        348          2          0
           200         10        349          3          0
           201         11        350          1        350
           203         12        351          2        233
           341         27        358          3          0
           341         34        352          3        456
           456         65        353          2        343
           456         65        354          2        343
             A          B          C          D          E
    ---------- ---------- ---------- ---------- ----------
           456         65        355          2        343
           677         53        356          2          0
           677         53        357          3          0

     
    14 ROWS selected.
     
    SQL>
     
     
  6. ylongbeach

    ylongbeach Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thank you.