Combine two resultset from different table

My Requirement is to display some of the columns in one table and some of the columns in another table in an html table. Though it has same coloumn id , value will be different. So, I cannot match this two. My query is as follows:

      SELECT time_stamp,queryresultset FROM table1 d WHERE dID = 'CP009'
      AND d.time_stamp >'2011-05-01 00:00:00' AND d.time_stamp < '2011-05-01 05:00:00'
      order by time_stamp
      UNION ALL
      SELECT time_stamp,cpuutil FROM table2 h WHERE hID='HS002'
      AND h.time_stamp >'2011-05-01 00:00:00' AND h.time_stamp < '2011-05-01 05:00:00'
      order by time_stamp

So, the time_stampe here I'm getting will vary just in milliseconds for both the table. But, I want it in one resultset. Though the time value varies in millisecones between the table, the开发者_开发技巧 number of rows will be equal. So, I have to bring this in a single resultset. I don't know whether it is possible to handle in sql query. Or I may have to try in java coding? Please guide me. Following is my sample html table.

          ----------------------------------------------------
           Time_stamp           Cpuutil        Queryresultset
          ----------------------------------------------------
           2011-03-09 12:00:00    2.3            9.8
           2011-03-09 12:15:00    5.3            4.5
           2011-03-09 12:30:00    4.3            9.3
           2011-03-09 12:45:00    2.3            9.2

I am afraid, I have difficulties with understanding your question, but it seems to me, you are looking for something as:

SELECT table1.time_stamp t1, table1.queryresultset, table2.time_stamp t2, table2.cpuutil

FROM table1 , table2

WHERE ABS(t1-t2)<100

  AND t1 >'2011-05-01 00:00:00' 

  AND t1 < '2011-05-01 05:00:00' 

ORDER by t1

Another posibility:

        `SELECT column list
        FROM table1
                    INNER JOIN table2
                    ON table1.col1=table2.col2
        WHERE criteria
        ORDER BY column list `

Have a nice day.

This sounds like a database conception mistake. If your two tables are linked logically, they should have a physical connector. If you can refactor your database model, the best solution is to add a table table0 holding the common factors between the two tables (or just an autoincrement id), and then to add an external key to table1 and table2. You need to insert a row into table0 first, then to insert a row into table1 and one into table2 using table0's key as an external key.

If you can't refactor the tables, that's too bad. Anyway, the easiest way to do what you want is to write java code:

• Open resultset1 from table 1
• Open resultset2 from table 2
• While resultset1 is not empty
  • fetch row1 from resultset1
  • fetch row2 from resultset2
  • generate html
• Close resultsets
• Done!

But this way is wrong and you will get problems as you advance further in your project.

Thanks for your responses. So I found out the query. It's working now.

   SELECT a.cpuutil,a.hostid,a.time_stamp, b.queryresultset, b.time_stamp AS tm 
   FROM table1 a, table2 b 
   WHERE a.hID = 'hs002' AND b.dID='cp011' 
   AND SUBSTR(a.time_Stamp,1,15) = SUBSTR(b.time_stamp,1,15) 
   AND a.time_stamp > '2011-05-10 00:00:00'
   AND a.time_stamp < '2011-05-10 14:00:00'