Hi,

I have two tables. Table1 has columns: jobid, time, cpu_perc. Table2 has
columns: cpu, time, load_ave.

The column 'time' on both tables hold the same values. However, Table1
holds other rows as well and so I need to select rows from both tables
that match 'time' that is in table2. The following query works:

SELECT time, cpu_perc FROM Table1 WHERE jobid = 53 AND time IN (SELECT
time FROM Table2 WHERE cpu = 400) ORDER BY time ASC;

How can I get 'load_ave' from the second table to be 'joined' with the
restul? Currently I only get the columns time and cpu_perc shown. I
tried doing a 'RIGHT OUTER JOIN' but it's giving me a cartesian result.
Can someone please help me out with the query?

Thanks!


Steve

Steve <nospam@nopes> wrote

Untested, but you could try:

select t1.time, t1.cpu_perc, t2.load_avg from (select time, load_avg
from table2 where cpu = 400) as t2(time, load_avg) where t1.time =
t2.time order by t1.time asc

If this is a frequent query, concider defining a view (or if your db
supports it , a table function) for t2


HTH
/Lennart

Lennart Jonsson wrote:
Reformated to make it readable:

select t1.time, t1.cpu_perc, t2.load_avg
from
(select time, load_avg
from table2 where cpu = 400) as t2(time, load_avg)
where t1.time = t2.time
order by t1.time asc

This is getting silly. Since you don't define t1 in the from clause, it
won't parse. And there's no reason to mess thing up with a subselect.


What the OP could try was:

SELECT t1.time, t1.cpu_perc, t2.load_avg
FROM Table1 t1
, Table2 t2
WHERE t1.jobid = 53
AND t2.cpu = 400
AND t1.time = t2.time
ORDER BY t1.time ASC;


--
Kristian Damm Jensen damm (at) ofir (dot) dk
Those who desire to give up Freedom in order to gain Security, will not
have, nor do they deserve, either one. -- Thomas Jefferson