MySQL join three queries without multiplying records

Question

I have three tables:
tblUnits [Unit_ID, Country]
tblEvents [Unit_ID, Month, Duration_Hrs]
tblMetrics [Unit_ID, Month, Service_Hrs]
and built 3 queries on them:
1.
SELECT Unit_ID
FROM tblUnits
WHERE City = 'NewYork'

with output:
Unit_ID
1
2
3

SELECT
COUNT(IF(Event_Type in ('X', 'Y'), 1, NULL)) as Ev_Count,
SUM(CASE
        WHEN Event_Type in ('X', 'Y') THEN Duration_Hrs
    END) Duration
FROM tblEvents
WHERE
Design = 'GoodDesign'
AND Month = 'May'
AND Unit_ID in (1,2,3)

with output:
Ev_Count | Duration
1        | 10

SELECT SUM(Service_Hrs) as SumSH
FROM tblMetrics
WHERE Month = 'May'
AND Unit_ID in (1,2,3)

with output:
SumSH
100

Now I would like to combine those 3 queries to get output like this:
Ev_Count | Duration | SumSH
1        | 10       | 100

My query with two left joins multiplies tblEvents and tblMetrics values.

forpas · Answer

You can't join the queries without getting additional rows which will return wrong aggregations and since your version of MySql does not support CTEs, you have to use twice the 1st query with the operator IN:
SELECT
  SUM(Event_Type IN ('X', 'Y')) Ev_Count,
  SUM(CASE WHEN Event_Type IN ('X', 'Y') THEN Duration_Hrs END) Duration,
  (
    SELECT SUM(Service_Hrs)
    FROM tblMetrics
    WHERE Month = 'May'
    AND Unit_ID IN (SELECT Unit_ID FROM tblUnits WHERE City = 'NewYork')
  ) SumSH
FROM tblEvents
WHERE Design = 'GoodDesign' AND Month = 'May' 
AND Unit_ID IN (SELECT Unit_ID FROM tblUnits WHERE City = 'NewYork')

Also, both your aggregated columns Ev_Count and Event_Type use the condition:
Event_Type IN ('X', 'Y')

so why don't you filter for this condition in the WHERE clause and avoid conditional aggregation:
SELECT
  COUNT(*) Ev_Count,
  SUM(Duration_Hrs) Duration,
  (
    SELECT SUM(Service_Hrs)
    FROM tblMetrics
    WHERE Month = 'May'
    AND Unit_ID IN (SELECT Unit_ID FROM tblUnits WHERE City = 'NewYork')
  ) SumSH
FROM tblEvents
WHERE Design = 'GoodDesign' AND Month = 'May' 
AND Event_Type IN ('X', 'Y')
AND Unit_ID IN (SELECT Unit_ID FROM tblUnits WHERE City = 'NewYork')

MySQL join three queries without multiplying records

One Answer

Add your own answers!

Ask a Question