SQL query to add data from other table when data is not matching

Question

I am creating a SQL query to add data from other table when data is not matching.

For e.g. in the data table shown here, data for Pol 2 in TFT column is not present and so in the current output due to following query Pol 2 data is not coming.

But I need Pol 2 data to come up over there, the data unmatching with data table can be NULL.

Expected output is the output that should be returned.

SQL query:

select distinct 
    b.Primary_Brand_Key as "Primary_Brand",
    a.Market "Market",
    Year "Year",
    Month "Month",
    Category "Category",
    Medium "Medium",
    b.Reach,                        
    concat(Month,' ',Year) "Period_Type",
    a.TFT as TFT,
    a.TG,   
    b.Perc as Perc, b.Value as Value
from 
    dbo.data  a
left join
    dbo.perc b on a.TFT = b.TFT
               and a.Market = b.Market 
               and a.TG = b.TG
               and a.Primary_Brand_Key = b.Primary_Brand_Key

Perc table:

Brand	TG	TFT	Market	Perc	Value
Lemon Tea	Female	Pol 1	Warsaw	88.61%	2.09
Lemon Tea	Female	Pol 2	Warsaw	64.64%	11.32
Lemon Tea	Female	Pol 3	Warsaw	27.41%	19.75

Data table:

Medium	Primary_Brand_Key	Month	Year	Market	Campaign	TG	TFT	Reach
TV	Lemon Tea	May	2021	Warsaw		Female	Pol 1	0.19
TV	Lemon Tea	May	2021	Warsaw		Female	Pol 3	0.11

Current output:

Brand	Market	Year	Month	Category	Medium	Reach	Period_Type	TFT	TG	Perc	Value
Lemon Tea	Warsaw	2021	May		TV	0.19	May-21	Pol 1	Female	88.61%	2.09
Lemon Tea	Warsaw	2021	May		TV	0.11	May-21	Pol 3	Female	27.41%	19.75

Desired output:

Brand	Market	Year	Month	Category	Medium	Reach	Period_Type	TFT	TG	Perc	Value
Lemon Tea	Warsaw	2021	May		TV	0.19	May-21	Pol 1	Female	88.61%	2.09
Lemon Tea	Warsaw	2021	May		NULL	NULL	May-21	Pol 2	Female	64.64%	11.32
Lemon Tea	Warsaw	2021	May		TV	0.11	May-21	Pol 3	Female	27.41%	19.75

Answer 1

Left outer join operation is being performed between Data and Perc. So Data has 2 records and during left outer join only 2 records will come in the output. So you can use as below.

-- #temp is perc
SELECT 'LEMON TEA' AS BRAND,'FEMALE' AS TG,'POL 1' AS TFT,'WARSAW' AS MARKET,'88.61%' AS PERC,2.09  AS VALUE 
INTO #TEMP UNION ALL
SELECT 'LEMON TEA' AS BRAND,'FEMALE' AS TG,'POL 2' AS TFT,'WARSAW' AS MARKET,'64.64%' AS PERC,11.32 AS VALUE UNION ALL
SELECT 'LEMON TEA' AS BRAND,'FEMALE' AS TG,'POL 3' AS TFT,'WARSAW' AS MARKET,'27.41%' AS PERC,19.75 AS VALUE 

-- #temp2 is data
SELECT 'TV' AS MEDIUM,'LEMON TEA' AS PRIMARY_BRAND_KEY,'MAY' AS MONTH,2021 AS YEAR,'WARSAW' AS MARKET, '' AS CAMPAIGN, 'FEMALE' AS TG ,'POL 1' AS TFT ,0.19 AS REACH 
INTO #TEMP2 UNION ALL
SELECT 'TV' AS MEDIUM,'LEMON TEA' AS PRIMARY_BRAND_KEY,'MAY' AS MONTH,2021 AS YEAR,'WARSAW' AS MARKET, '' AS CAMPAIGN, 'FEMALE' AS TG ,'POL 3' AS TFT ,0.11 AS REACH

SELECT DISTINCT A.BRAND AS "PRIMARY_BRAND",
A.MARKET "MARKET",
B.YEAR "YEAR",
B.MONTH "MONTH",
CAMPAIGN "CATEGORY",
MEDIUM "MEDIUM",
B.REACH,                        
CONCAT(MONTH,' ',YEAR) "PERIOD_TYPE",                        
A.TFT AS TFT,
A.TG,   
A.PERC AS PERC , A.VALUE AS VALUE
FROM #TEMP  A
LEFT JOIN #TEMP2 B
ON A.TFT = B.TFT
AND A.MARKET = B.MARKET 
AND A.TG=B.TG
AND A.BRAND=B.PRIMARY_BRAND_KEY