Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    11

    Unanswered: Fetching Data from Different table

    Fetching Data from Different table
    TABLE1
    ID Name course Code
    1 Jo WBT 11
    2 saha ICA 22
    3 Asw BCT 33


    TABLE 2
    ID Score Course Code
    2 100 ABT 22
    5 300 BTA 33
    3 200 CBA 33
    6 50 NGT 33

    Query the table using code = 33
    RESULT TABLE
    ID Name Course Score Code
    3 asw BCT 33
    3 CBA 200 33
    5 BTA 300 33
    6 NGT 50 33


    is it possible(Appending 2 table records row vise) ?

  2. #2
    Join Date
    Oct 2008
    Posts
    82

    Arrow

    Use joins....

  3. #3
    Join Date
    Jan 2004
    Location
    UK
    Posts
    11
    select * from tab1

    A B C D
    ---------- ---------- ---------- ----------
    2 100 ABT 22
    5 300 BTA 33
    3 200 CBA 33
    6 50 NGT 33

    select * from tab2

    A B C D
    ---------- ---------- ---------- ----------
    3 asw BCT 33
    3 CBA 200 33
    5 BTA 300 33
    6 NGT 50 33


    SELECT TAB1.*
    FROM TAB1 inner JOIN TAB2
    ON TAB1.A = TAB2.A
    where tab2.D='33'"
    order by 1
    A B C D
    ---------- ---------- ---------- ----------
    3 200 CBA 33
    3 200 CBA 33
    5 300 BTA 33
    6 50 NGT 33

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Compared the column names and values of the result with those of table1 and table2,
    I thought that the result might be like...
    Code:
    ID Name Course Score Code
    3  asw  BCT          33
    3       CBA      200 33
    5       BTA      300 33
    6       NGT       50 33
    If so, union all may be an answer.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     TABLE1(ID , Name , course , Code) AS (
    VALUES
      (1 , 'Jo'   , 'WBT' , '11')
    , (2 , 'saha' , 'ICA' , '22')
    , (3 , 'Asw'  , 'BCT' , '33')
    )
    , TABLE2(ID , Score , Course , Code) AS (
    VALUES
      (2 , 100 , 'ABT' , '22')
    , (5 , 300 , 'BTA' , '33')
    , (3 , 200 , 'CBA' , '33')
    , (6 ,  50 , 'NGT' , '33')
    )
    (
    SELECT id
         , name
         , course
         , CAST(NULL AS INT) AS score
         , code
     FROM  table1
     WHERE code = 33
    UNION ALL
    SELECT id
         , CAST(NULL AS VARCHAR(3) ) AS name
         , course
         , score
         , code
     FROM  table2
     WHERE code = 33
    )
     ORDER BY
           id
         , name
    ;
    ------------------------------------------------------------------------------
    
    ID          NAME COURSE SCORE       CODE
    ----------- ---- ------ ----------- ----
              3 Asw  BCT              - 33  
              3 -    CBA            200 33  
              5 -    BTA            300 33  
              6 -    NGT             50 33  
    
      4 record(s) selected.

    Generally speaking, an UNION ALL can be replaced by a FULL OUTER JOIN
    (though, it may be not so efficient than union all),
    like this...

    Example 2:
    Code:
    SELECT COALESCE(t1.id     , t2.id    ) AS id
         , t1.name
         , COALESCE(t1.course , t2.course) AS course
         , t2.score
         , COALESCE(t1.code   , t2.code  ) AS code
     FROM  table1 t1
     FULL  OUTER JOIN
           table2 t2
       ON  0 <> 0
     WHERE t1.code = 33
       OR  t2.code = 33
    /*     33 IN (t1.code , t2.code) */
     ORDER BY
           t1.id
         , t2.id
    ;
    ------------------------------------------------------------------------------
    
    ID          NAME COURSE SCORE       CODE
    ----------- ---- ------ ----------- ----
              3 Asw  BCT              - 33  
              3 -    CBA            200 33  
              5 -    BTA            300 33  
              6 -    NGT             50 33  
    
      4 record(s) selected.
    Last edited by tonkuma; 08-03-11 at 21:18. Reason: Change sequences of columns in COALESCE in Example 2. Add alternate condition in comment in Example 2.

  5. #5
    Join Date
    Jul 2011
    Posts
    11
    Thank u So mcuh Tokuma !!!!
    It solved my Purpose !!!!

  6. #6
    Join Date
    Jul 2011
    Posts
    11
    Thank u for all who took so much to reply me

Tags for this Thread

Posting Permissions

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