I'm doing something wrong and I can't figure it out.
I have a table with hourly data - so for all intents and purposes, each row has a datetime field and an integer field.
I have two queries - one produces 9 rows and the other produces 6 rows:
Q1 = SELECT date, value FROM table WHERE date>=CURDATE()
Q2 = SELECT DATE_SUB(date,INTERVAL 3 HOUR) as date2, value FROM table WHERE date>=DATE_ADD(CURDATE(),INTERVAL 3 HOUR)
I won't go into an explanation for why the second query is shifting time by an hour as I'm simplifying my description of the table, but it's a requirement
so independently, those queries work perfectly. But I want them in one query, so I tried this:
SELECT sq1.date, sq1.value, sq2.value
FROM
(SELECT date, value FROM table WHERE date>=CURDATE()) AS sq1,
(SELECT DATE_SUB(date,INTERVAL 3 HOUR) as date2, value FROM table WHERE date>=DATE_ADD(CURDATE(),INTERVAL 3 HOUR)) AS sq2
WHERE sq1.date=sq2.date2
which works exactly the same as:
SELECT sq1.date, sq1.value, sq2.value
FROM
(SELECT date, value FROM table WHERE date>=CURDATE()) AS sq1
INNER JOIN
(SELECT DATE_SUB(date,INTERVAL 3 HOUR) as date2, value FROM table WHERE date>=DATE_ADD(CURDATE(),INTERVAL 3 HOUR)) AS sq2
ON sq1.date=sq2.date2
That works great too -- EXCEPT, it only returns the 6 rows where both queries match. I really want a LEFT JOIN so I can get 9 from the first column and 6 from the second column. However, if I change the query to a LEFT JOIN, it basically won't complete (maybe it does but I didn't wait to see how long it would take as it was already too long)
SELECT sq1.date, sq1.value, sq2.value
FROM
(SELECT date, value FROM table WHERE date>=CURDATE()) AS sq1
LEFT JOIN
(SELECT DATE_SUB(date,INTERVAL 3 HOUR) as date2, value FROM table WHERE date>=DATE_ADD(CURDATE(),INTERVAL 3 HOUR)) AS sq2
ON sq1.date=sq2.date2
What am I doing wrong here?