locked
how to get desired result RRS feed

  • Question

  • hi

    i have a ques how do i get the desired result from two tables

    table number one   --  trans

    mid    amount

    300     400

    720      90

    21         40

    300        50

    ________________

    table number two -- usage

    mid   amount

    300   100

    400     50

    200      40

    21        4

    _____________________

    want the result to look like this

               (table 1)     (table 2)

    mid     amount1         amount 2

    300     400            0            //this amount is from table number one

    300      0            100         // amount from table number 2

    300      50             0           // amount from table number   1

    Tuesday, August 14, 2012 5:10 AM

Answers

  • Hello,

    You can query the two table with separate SELECT statements and combine the result using a UNION ALL operator:

    SELECT mid, amount AS amount1, 0 AS amount2
    FROM table1
    WHERE mid = 300
    
    UNION ALL
    
    SELECT mid, 0 AS amount1, amount AS amount2
    FROM table2
    WHERE mid = 300


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by Janos BerkeMVP Tuesday, August 14, 2012 7:35 AM
    • Marked as answer by avijitsingh11 Friday, August 17, 2012 7:47 AM
    Tuesday, August 14, 2012 6:28 AM

All replies

  • Hello,

    You can query the two table with separate SELECT statements and combine the result using a UNION ALL operator:

    SELECT mid, amount AS amount1, 0 AS amount2
    FROM table1
    WHERE mid = 300
    
    UNION ALL
    
    SELECT mid, 0 AS amount1, amount AS amount2
    FROM table2
    WHERE mid = 300


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by Janos BerkeMVP Tuesday, August 14, 2012 7:35 AM
    • Marked as answer by avijitsingh11 Friday, August 17, 2012 7:47 AM
    Tuesday, August 14, 2012 6:28 AM
  • thnksss a milion sir

    this is exactly wat i needed

    Friday, August 17, 2012 7:50 AM