Because both your queries return the same number (and type) of fields, the can be combinied with UNION ALL. The ORDER BY is only needed once at then end of the query.
To see the difference between indoor and outdoor this could be added to your query.
SELECT 'out' as door,DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_out_temp
Mysql has no full out join, this is why it hast to be simulated.
So iz os quit ugly.
SELECT IFNULL(ti.time,tou.time) time,ti.avg_in_temp,tou.avg_out_temp
((SELECT DATE_FORMAT(`timestamp`, '%Y-%m-%d %H') timecon
,MIN(DATE_FORMAT(`timestamp`, '%H:00')) AS time
, round(avg(temp), 1) as avg_in_temp
WHERE `timestamp` >= now() - INTERVAL 1 ...
One way you can achieve this is via federation. In short words, if you want to access db_b from db_a, you catalog db_b in instance of db_a. You then create nicknames in db_a for the tables in db_b that you want to access. The nicknames can with some restrictions be treated as tables, so you can join a table in db_a with a table in db_b.
SELECT DISTINCT `int`, SUBSTRING_INDEX(SUBSTRING_INDEX(`set_2`, ',', `mask`), ',', -1) `set`
-- generate masks based on SET values list count
(SELECT 1 `mask` UNION
SELECT 2 UNION
SELECT 3) `mask`
WHERE `set_2` & 1;
If server version allows build mask in recursive CTE (dynamic count may be used)
JOIN stations AS subways
This is called an alias. You aliased stations as subways. So for the rest of the query (ON, SELECT part) the table is called subways and you can only reference it as subways.
Never seen a negative OID before. This is a first-class "crime scene"!
... attrelid=-1519044407::oid ...
... rel.oid = -1519044407::oid ...
2^32 - 1519044407 = 2775922889
And we have verified that OID 2775922889 indeed exists in your DB. Tests:
SELECT * FROM pg_class WHERE oid = 2775922889;
SELECT * FROM pg_class WHERE oid = '-1519044407';
You need one row per product. So GROUP BY needed. And the query will look like
SELECT product_id, (expression for availability)
GROUP BY product_id
You have 3 general variants for the amount - 0, 1 or >1 (if some record is absent we must assume its amount value is 0). For to formalize we will substract 1 and get the sign (it will compact ...