remarsh
remarsh

Reputation: 658

Return additional column value for MySQL Union Query

I have two queries that get the:

  1. Most recent snow measurement (within 1 hour)
  2. And a measurement taken 24 hours ago.

When I UNION the queries, I am expecting the two measurements to be in separate columns, but they are actually returned in separate (duplicate) rows. See output below.

SELECT snowfall.cms as now_snow, resorts.*, regions.name
FROM  snowfall
INNER JOIN resorts on resorts.id = snowfall.resort_id
INNER JOIN  regions ON resorts.region_id = regions.id
WHERE snowfall.timestamp >= SUBDATE( NOW( ) , INTERVAL 1 HOUR )
GROUP BY resorts.id
UNION
SELECT snowfall.cms as 24hr_snow, resorts.*, regions.name
FROM  snowfall
INNER JOIN resorts on resorts.id = snowfall.resort_id
INNER JOIN  regions ON resorts.region_id = regions.id
WHERE snowfall.timestamp >= SUBDATE( NOW() , INTERVAL 1 DAY)
GROUP BY resorts.id
ORDER BY now_snow DESC

I am getting a result of:

now_snow | resorts.name | ...
========================================
20       | The Mountain
15       | The Mountain
18       | The Hill
102      | The Hill

But was expecting a result of:

now_snow | 24hr_snow | resorts.name | ...
========================================
20       | 15        | The Mountain
18       | 102       | The Hill

Is UNION correct in this scenario? How can I achieve the desired output?

Upvotes: 1

Views: 89

Answers (1)

crthompson
crthompson

Reputation: 15865

A UNION will append rows to your query. In order to broaden your query you need a join.

I would suggest a self join in this case. One snowfall for daily, one for hourly. Both joined on resort id and grouped by resort name.

SELECT sum(hourly.cms) as now_snow, sum(daily.cms) as 24hr_snow, resorts.name
FROM  
  snowfall daily
  INNER JOIN resorts on resorts.id = daily.resort_id
  INNER JOIN regions ON resorts.region_id = regions.id
  INNER JOIN snowfall hourly on resorts.id = hourly.resort_id
WHERE daily.timestamp >= SUBDATE( NOW() , INTERVAL 1 DAY)
  AND hourly.timestamp >= SUBDATE( NOW( ) , INTERVAL 1 HOUR )
GROUP BY resorts.name
ORDER BY now_snow DESC

You didnt use an aggregate in your post and mysql may allow for that, but I've added it here for clarity.

Upvotes: 2

Related Questions