SQL: Combining two query results that have a common column but each has its own specific unique column

user_2315621 0
Viewed 99

I'm struggling to find the right approach to what I want to achieve. So here's what I have:

Query A gives me the following result:

TrainingID totalPass
2 5
3 7
4 8

Query B gives me the following

TrainingID totalFail
2 3
6 7
7 9

The result I'd like to have is the following:

TrainingID totalPass totalFail
2 5 3
3 7 Null
4 8 Null
6 Null 7
7 Null 9

I tried emulating an outer join in MySQL by combining left and right join with an union but the result is not quite what I want, but the closest I could get into. Perhaps my main issue is that I don't know a terminology to describe what is exactly this operation I'm trying to do so I don't know what to search for.

union mysql sql
4 Answers

I would use a full outer join. Here is an example:

declare @p table (TrainingID int,   totalPass int)
insert into @p
values
(2, 5)
,(3,    7)
,(4,    8)

declare @f table (TrainingID int,   totalFail int)

insert into @f
values
(2, 3)
,(6,    7)
,(7,    9)

select TrainingID = COALESCE(p.TrainingID,f.TrainingID)
    , totalPass = SUM(p.totalPass)
    , totalFail = SUM(f.totalFail)
from @p p
    full outer join @f  f on p.TrainingID=f.TrainingID
group by COALESCE(p.TrainingID,f.TrainingID)

Note: COALESCE is a function that takes the first non-null value.

Result:

TrainingID  totalPass   totalFail
2              5          3
3              7          NULL
4              8          NULL
6              NULL       7
7              NULL       9
user_33252900

In the 1st query add a NULL column for totalFail and in the 2nd query add a NULL column for totalPass.
Use UNION ALL to get all rows and aggregate:

SELECT TrainingID,
       MAX(totalPass) AS totalPass,
       MAX(totalFail) AS totalFail
FROM (
  SELECT TrainingID, totalPass, NULL AS totalFail
  FROM QueryA
  UNION ALL
  SELECT TrainingID, NULL, totalFail
  FROM QueryB
) t
GROUP BY TrainingID

See a simplified demo.

user_104988280

Try this

select coalesce(a.trainingId, b.trainingId)
,a.totalPass
,b.totalPass
from (QueryA) a full outer join (queryB) b on (queryA.trainingId =    b.trainingId
user_70363860

MySQL doesn't support full join -- which is what you want. But you can do this without too much trouble using union all. But let's define your queries using CTEs first:

with a as (
      . . .
     ),
     b as (
      . . .
     )
select trainingId, a.totalPass, b.totalFail
from a left join
     b
     using (trainingId)
union all
select trainingId, null, b.totalFail
from b left join
     a
     using (trainingId)
where a.trainingId is null;
user_11440350
Related