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!

SQL Join two tables

Discussion in 'General' started by virusx1984, Mar 1, 2011.

  1. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    I have two table:
    tab_1:
    po_id|pn|received
    0001|A|1000
    0002|A|3500
    0003|A|4500


    and tab_2:

    pn|delivered
    A|4000

    and I want the result:

    po_id|pn|received|delivered|left
    0001|A|1000|1000|0
    0001|A|3500|3000|500
    0001|A|4500|0|4500

    It seems that it can't be solve with sql text, if tab_2 didn't have the column "po_id" which can be use to connect to tab_1's "po_id".

    Someone can help me?
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi, for starters, what is the column "left" in the result that you are expecting?
     
  3. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    left = received - delivered
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Where, then, did the 1000 and 3000 come from? All you have for delivered is 4000 in a single entry.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This might be what you are looking for in straight SQL.

    Code (SQL):

    SQL> SELECT * FROM TAB_1 ORDER BY PN ,  PO_ID;

    PO_ID P   RECEIVED
    ----- - ----------
    0001  A       1000
    0002  A       3500
    0003  A       4500
    001   B       1000
    002   B       2500
    003   B       5000
    004   B       3000

    7 ROWS selected.

    SQL> SELECT * FROM TAB_2 ORDER BY PN;

    P  DELIVERED
    - ----------
    A       4000
    B       5000

    SQL> SELECT T1.PO_ID , T1.PN , T1.RECEIVED,
                 SUM(T1.RECEIVED) OVER (PARTITION BY T1.PN ORDER BY T1.PO_ID ) SUMRECEIVED,
                 T2.DELIVERED
      FROM TAB_1 T1 ,
           TAB_2 T2
     WHERE T1.PN = T2.PN  2    3    4    5    6  ;

    PO_ID P   RECEIVED SUMRECEIVED  DELIVERED
    ----- - ---------- ----------- ----------
    0001  A       1000        1000       4000
    0002  A       3500        4500       4000
    0003  A       4500        9000       4000
    001   B       1000        1000       5000
    002   B       2500        3500       5000
    003   B       5000        8500       5000
    004   B       3000       11500       5000

    7 ROWS selected.

    SQL> SELECT PO_ID,
      2         PN,
      3         RECEIVED,
      4         GREATEST (CASE WHEN SUMRECEIVED<= DELIVERED THEN SUMRECEIVED
      5                  WHEN SUMRECEIVED> DELIVERED THEN  DELIVERED - LAG(SUMRECEIVED,1,0)OVER (ORDER BY PO_ID )
      6                  END  , 0) DELIVERED,
      7         RECEIVED - GREATEST (CASE WHEN SUMRECEIVED<= DELIVERED THEN  RECEIVED
      8                  WHEN SUMRECEIVED> DELIVERED THEN  DELIVERED - LAG(SUMRECEIVED,1,0)OVER (ORDER BY PO_ID )
      9                  END  , 0) LEFT
     10  FROM (SELECT T1.PO_ID , T1.PN , T1.RECEIVED,
     11               SUM(T1.RECEIVED) OVER (PARTITION BY T1.PN ORDER BY T1.PO_ID ) SUMRECEIVED,
     12               T2.DELIVERED
            FROM TAB_1 T1 ,
     13   14               TAB_2 T2
     15         WHERE T1.PN = T2.PN );

    PO_ID P   RECEIVED  DELIVERED       LEFT
    ----- - ---------- ---------- ----------
    0001  A       1000       1000          0
    0002  A       3500       3000        500
    0003  A       4500          0       4500
    001   B       1000       1000          0
    002   B       2500       3500          0
    003   B       5000       1500       3500
    004   B       3000          0       3000

    7 ROWS selected.

    SQL>

     
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Ooops.. a small mistake....

    Code (SQL):

    SQL> SELECT PO_ID,
           PN,
      2    3         RECEIVED,
      4         GREATEST (CASE WHEN SUMRECEIVED<= DELIVERED THEN RECEIVED
      5                  WHEN SUMRECEIVED> DELIVERED THEN  DELIVERED - LAG(SUMRECEIVED,1,0)OVER (ORDER BY PO_ID )
      6                  END  , 0) DELIVERED,
      7         RECEIVED - GREATEST (CASE WHEN SUMRECEIVED<= DELIVERED THEN  RECEIVED
      8                  WHEN SUMRECEIVED> DELIVERED THEN  DELIVERED - LAG(SUMRECEIVED,1,0)OVER (ORDER BY PO_ID )
      9                  END  , 0) LEFT
     10  FROM (SELECT T1.PO_ID , T1.PN , T1.RECEIVED,
     11               SUM(T1.RECEIVED) OVER (PARTITION BY T1.PN ORDER BY T1.PO_ID ) SUMRECEIVED,
     12               T2.DELIVERED
     13          FROM TAB_1 T1 ,
     14               TAB_2 T2
     15         WHERE T1.PN = T2.PN )    ;

    PO_ID P   RECEIVED  DELIVERED       LEFT
    ----- - ---------- ---------- ----------
    0001  A       1000       1000          0
    0002  A       3500       3000        500
    0003  A       4500          0       4500
    001   B       1000       1000          0
    002   B       2500       2500          0
    003   B       5000       1500       3500
    004   B       3000          0       3000

    7 ROWS selected.

    SQL>

     
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    hmhm , Another small mistake in the query again.

    Code (SQL):

    SQL>    SELECT PO_ID,
      2            PN,
      3            RECEIVED,
      4            GREATEST (CASE WHEN SUMRECEIVED<= DELIVERED THEN RECEIVED
      5                             WHEN SUMRECEIVED> DELIVERED THEN  DELIVERED - LAG(SUMRECEIVED,1,0)OVER (ORDER BY  PN ,PO_ID )
      6                             END  , 0) DELIVERED,
      7            RECEIVED - GREATEST (CASE WHEN SUMRECEIVED<= DELIVERED THEN  RECEIVED
      8                                 WHEN SUMRECEIVED> DELIVERED THEN  DELIVERED - LAG(SUMRECEIVED,1,0)OVER (ORDER BY PN ,PO_ID )
      9                                 END  , 0) LEFT
     10     FROM (SELECT T1.PO_ID , T1.PN , T1.RECEIVED,
     11                  SUM(T1.RECEIVED) OVER (PARTITION BY T1.PN ORDER BY T1.PN , T1.PO_ID ) SUMRECEIVED,
     12                  T2.DELIVERED
     13             FROM TAB_1 T1 ,
     14                  TAB_2 T2
     15            WHERE T1.PN = T2.PN );

    PO_ID P   RECEIVED  DELIVERED       LEFT
    ----- - ---------- ---------- ----------
    001   A       1000       1000          0
    002   A       3500       3000        500
    003   A       4500          0       4500
    001   B       1000       1000          0
    002   B       2500       2500          0
    003   B       5000       1500       3500
    004   B       3000          0       3000

    7 ROWS selected.

    SQL>

     
     
    virusx1984 likes this.
  8. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    You're genius!!!!!!

    I have to spend some time to understand it.