Weird MySQL JOIN where I want to include matches AND no matches from join-CodePudding

I have a need to get a specific result for a query... I believe the solution is a self-join of the same table... but it's not working as I would expect.

A bit of background: the "timeframe" column denotes a time increment dataset. "15m" is a dataset of 15-minute datapoints. "1h" is a dataset of 1-hour datapoints.

Here's the table's contents:

priceID | price_time          | timeframe | price | thing
1       | 2020-01-01 10:00:00 | "15m"     | 150   | 0
2       | 2020-01-01 10:15:00 | "15m"     | 155   | 1
3       | 2020-01-01 10:30:00 | "15m"     | 140   | 0
4       | 2020-01-01 10:45:00 | "15m"     | 123   | 1
5       | 2020-01-01 11:00:00 | "15m"     | 159   | 0
6       | 2020-01-01 10:00:00 | "1h"      | 150   | 1
7       | 2020-01-01 11:00:00 | "1h"      | 159   | 0

Here's what I need as a recordset. I want one row per unique price_time, and a unique column for all other columns, grouped by the timeframe value.

price_time          | timeframe_15m | timeframe_1h | price_15m | price_1h | thing_15m | thing_1h
2020-01-01 10:00:00 | "15m"         | "1h"         | 150       | 150      | 0         | 1
2020-01-01 10:15:00 | "15m"         | NULL         | 155       | NULL     | 1         | NULL
2020-01-01 10:30:00 | "15m"         | NULL         | 140       | NULL     | 0         | NULL
2020-01-01 10:45:00 | "15m"         | NULL         | 123       | NULL     | 1         | NULL
2020-01-01 11:00:00 | "15m"         | "1h"         | 159       | 150      | 0         | 0

Here's what I thought would work, but doesn't.

SELECT  
 c.price_time
 ,c.timeframe AS price_time_15m
 ,c.price AS price_15m
 ,c.thing AS thing_15m
 ,o.timeframe AS timeframe_1h
 ,o.price AS price_1h
 ,o.thing AS thing_1h
 
FROM    tbl_prices c LEFT OUTER JOIN tbl_prices o ON c.price_time = o.price_time
WHERE   c.timeframe = '15m'
 AND    o.timeframe = '1h'

I've spent quite a while on this and am officially stuck! Any help would be greatly appreciated!

CodePudding user response：

Use conditional aggregation:

SELECT price_time,
       MAX(CASE WHEN timeframe = '"15m"' THEN timeframe END) timeframe_15m,
       MAX(CASE WHEN timeframe = '"1h"' THEN timeframe END) timeframe_1h,
       MAX(CASE WHEN timeframe = '"15m"' THEN price END) price_15m,
       MAX(CASE WHEN timeframe = '"1h"' THEN price END) price_1h,
       MAX(CASE WHEN timeframe = '"15m"' THEN thing END) thing_15m,
       MAX(CASE WHEN timeframe = '"1h"' THEN thing END) thing_1h       
FROM tbl_prices
GROUP BY price_time;

See the demo.