SQL Joining Multiple Tables into a Data Extension [closed]

Question

I have two Data Extensions with fields "MID" and "Total". And a third DataExtension with MID and Name. I want to make a NEW DataExtension that has these three tables joined into one report.

The table below the three is the final output I want.

I'm trying to use SubQuery in the SELECT but can't get it to work.

SELECT
    hm.HM_Name,
    hm.MID,
    (SELECT s.Total FROM [TH_Total_Sent_BU] s GROUP BY s.MID) AS TotalEmail,
    (SELECT s.Total FROM [TH_Total_SMS_BU] sms GROUP BY sms.MID) AS TotalSMS,
    SUM(
        (SELECT s.Total FROM [TH_Total_Sent_BU] s GROUP BY s.MID),
        (SELECT s.Total FROM [TH_Total_SMS_BU] sms GROUP BY sms.MID)
        ) AS SubTotal
FROM [HM_MID] hm

Answer 1

You should simply use LEFT JOIN and then add up two fields to get a "SubTotal" value -

SELECT
a.MID,
a.HM_Name,
b.Total AS TotalSMS,
c.Total AS TotalEmail,
b.Total + c.Total AS SubTotal
FROM [HM_MID] a
LEFT JOIN [TH_Total_SMS_BU] b ON a.MID = b.MID
LEFT JOIN [TH_Total_Sent_BU] c ON a.MID = c.MID