Nested MySQL subqueries

Mucello

I'm trying to work with nested subqueries in MySQL and I'm getting pretty confused.

To keep it simple, I have a table with a datetime field, temperature and pressure.

It looks like this:
ob, temp, pres
2005-04-01 14:00:00, 27.000, 1004.00
... lots more rows like this

Here's the problem. I want to make a query that will give me the ob, temp, pres AND the ob, temp, pres of 12 hours previous. I want this to be returned as one row.

The reason for this is to calculate sea-level pressure from station pressure, which means I have to have the temperature and station pressure 12 hours prior to whatever datetime I'm working with and bla bla bla. If I can just get this query down pat I'll have it made.

Any advice on how to build such a subquery of a subquery?

In essence, I want:
SELECT ob, temp, pres, ob12, temp12, pres12
FROM (SELECT ob-12 hours ago as ob12, temp minus 12 hours as temp12, pres minus 12 hours as pres12) as query

The above is the basic logic of what I want to do, but how do I make it happen?

Dr_John

I'd guess your logic is back to front. The subquery can't get the 12 hours ago value as the current value hasn't been retrieved yet.

Try logic like this

select x, y
from (select x from table)
where y = x+12;

Perhaps.

Mucello

You're right-- my understanding of subqueries is that they are executed from the inside out. So my "in essence" query is backwards.

Instead, I'm looking for something like:

SELECT ob,temp,pres, ob minus 12 hours, temp minus 12 hours, pres minus 12 hours FROM
(SELECT ob,temp,pres FROM TABLE) as base;

Dr_John

The following is more likely to work however.

Select a.ob, b.ob, a.temp, b.temp, a.press, b.press, a.time, b.time
from table a, table b
where b.time =a.time - 12
and a.time = X;

X being the later of teh two times. I'm assuming you will be inputting X, to get a single row back. If you wish to do this for ALL rows in the table, then miss out "and a.time = X"

This is a join of a table to itself, hence the use of aliases is compulsary.