Right approach to use join for particular scenario in sql

I have a table that looks like this:

CREATE TABLE #C (grpType varchar(10), CPTCode varchar(10) NULL, Month int NULL, MTD money NULL, MonthCount int NULL, YTD money NULL, YearCount int NULL, Code varchar(10) NULL)


It has the following data in it:
grpType CPTCode Month MTD MonthCount YTD YearCount Code
Month 76800 5 1321.61 27 6574.54 82 76800
Month 76856 5 246.01 3 380.64 6 76856
Month 76881 5 9778.95 131 50682.59 509 76881
Month 76942 5 22467.33 190 116663.58 674 76942



Then I have another table:

CREATE TABLE #Prod (grpType varchar(10), TotalCharges money NULL, TotalUnits float NULL, RVU float NULL, Code varchar(10) NULL, CPTCode varchar(10) NULL)


This table has the following data in it:

grpType TotalCharges TotalUnits RVU Code CPTCode
Month 6100.00 12 0 76800 76800
Month -475.00 -1 0 76880 76880
Month 38749.00 81 0 76881 76881
Month 54733.00 114 0 76942 76942

What I need to do with these tables is join them so that every CPTCode shows and I get the MTD, TotalCharges and TotalUnits along with the CPTCodes. So my end data should look like this:

CPTCode MTD TotalCharges TotalUnits
76800 1321.61 6100.00 12
76856 246.01 NULL NULL
76880 NULL -475.00 -1
76881 9778.95 38749.00 81
76942 22497.33 54733.00 114
Solution:

 What would be the best join to accomplish this? I have tried the left join and it didn't come out correctly. So I am a little lost. I also thought a pivot query may be what I need?

Based on sample results, we want a result for any CPTCode that appears in either table, with the appropriate fields (the fields from the other table) set to NULL if that table doesn't have a record for the same CPTCode.

My first reaction is that you need a UNION of two queries: a LEFT OUTER JOIN and a RIGHT OUTER JOIN, where each of the sub-queries has a WHERE clause to exclude records where the secondary table isn't NULL. However, that will exclude those CPTCodes that do appear in both tables.

So, one approach is to use three sub-queries: a LEFT OUTER JOIN and a RIGHT OUTER JOIN as described above, plus an INNER JOIN to get the matching records:
SELECT CPTCode, MTD, TotalCharges, TotalUnits
FROM
(SELECT #C.CPTCode, #C.MTD, #Prod.TotalCharges, #Prod.TotalUnits
FROM #C LEFT OUTER JOIN #Prod
ON #C.CPTCode = #Prod.CPTCode
WHERE #Prod.CPTCode IS NULL)
UNION
(SELECT #C.CPTCode, #C.MTD, #Prod.TotalCharges, #Prod.TotalUnits
FROM #C RIGHT OUTER JOIN #Prod
ON #C.CPTCode = #Prod.CPTCode
WHERE #C.CPTCode IS NULL)
UNION
(SELECT #C.CPTCode, #C.MTD, #Prod.TotalCharges, #Prod.TotalUnits
FROM #C INNER JOIN #Prod
ON #C.CPTCode = #Prod.CPTCode)