Sql-server – Full Outer Join shows NULL when Group By is used

group byjoin;nullsql server

I have 4 subqueries and each of the subquery is grouped by 'Group name'. The attempt is to have each subquery as a column all grouped by the 'Group Name'. Here is the query: 

select  
    coalesce(co.group_name, requests.group_Name, incidents.group_Name, problems.group_Name) as 'SD Groups'
    , isnull(co.co, '') as 'CO'
    , isnull(incidents.incidents, '' ) as 'Inc'
    , isnull(problems.problems, '') as 'Prob'
    , isnull(requests.requests, '') as 'Rqst'
from
    (
    select 
        groups.last_name AS Group_Name   
        ,count(chg_ref_num)  AS 'CO'
    from chg
    left join   ca_contact groups 
        on chg.group_id = groups.contact_uuid 
    left join   ca_contact assignee 
        on chg.assignee = assignee.contact_uuid 
    left join   ca_company cc 
        on  assignee.company_uuid = cc.company_uuid                       
    where   
        groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse') 
        and status = 'CL'
        and     convert(varchar, dateadd(hh,-8,dateadd(ss,chg.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)
        and     parent is NULL  
        and     cc.company_name = 'XYZ' 
    group by    groups.last_name
        ) as CO
full outer join 
    (
    select                         
        groups.last_name AS Group_Name   
        ,count(ref_num) AS Requests
    from call_req cr                                
    left join   ca_contact groups 
        on cr.group_id = groups.contact_uuid  
    left join   ca_contact assignee 
        on cr.assignee = assignee.contact_uuid  
    left join   ca_company cc 
        on  assignee.company_uuid = cc.company_uuid                       
    where   
        groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse')                         
        and     cr.status in ('CL')     
        and     convert(varchar, dateadd(hh,-8,dateadd(ss,cr.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)                    
        and     cr.parent is NULL   
        and     cr.type = 'R'   
        and     cc.company_name = 'XYZ'                 
    group by    groups.last_name, cr.type
        ) as Requests
    on co.group_name = requests.group_name
full outer join
    (
    select                                 
        groups.last_name AS Group_Name   
        ,count(ref_num) AS Problems
    from call_req cr                                
    left join   ca_contact groups 
        on cr.group_id = groups.contact_uuid
    left join   ca_contact assignee 
        on cr.assignee = assignee.contact_uuid  
    left join   ca_company cc 
        on  assignee.company_uuid = cc.company_uuid                               
    where   
        groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse')                         
        and     cr.status in ('CL')     
        and     convert(varchar, dateadd(hh,-8,dateadd(ss,cr.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)                    
        and     cr.parent is NULL   
        and     cr.type = 'P'   
        and     cc.company_name = 'XYZ'             
    group by    groups.last_name, cr.type
    ) as Problems
    on  requests.group_name = problems.group_name

full outer join 
    (
    select                                 
        groups.last_name AS Group_Name   
        ,count(ref_num) AS Incidents
    from call_req cr                                
    left join   ca_contact groups 
        on cr.group_id = groups.contact_uuid  
    left join   ca_contact assignee 
        on cr.assignee = assignee.contact_uuid  
    left join   ca_company cc 
        on  assignee.company_uuid = cc.company_uuid                           
    where   
        groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse')                         
        and     cr.status in ('CL')     
        and     convert(varchar, dateadd(hh,-8,dateadd(ss,cr.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)                    
        and     cr.parent is NULL   
        and     cr.type = 'I'   
        and     cc.company_name = 'XYZ'                 
    group by    groups.last_name, cr.type
    ) as Incidents
    on requests.group_name = incidents.group_name
order by 
    'SD Groups' asc

Here are the results:

Group_Name                                     CO  Inc Prob Rqst
8197 Qlikview Support                           0   1   0   7
8202 OBIEE-BIP                                  0   4   0   11
8205 BI SAS                                     0   11  1   11
8206 BI CCBI                                   10   17  0   43
8208 BI ePlan Reporting and Support             0   0   0   4
8211 BI Data Quality                            0   0   0   12
8212 BI EPM Report Architect/Developer          0   3   1   5
8214 BI EPM User Experience                     0   2   0   0
8214 BI EPM User Experience                     0   0   1   0
8215 BI EPM OLAP Architect/Developer            0   15  0   2
8219 BI Data Warehouse                         16   71  4   13

Notice two rows for Group 8214. Each group should be represented once and the desired result for Group 8214 should be:

Group_Name                                     CO  Inc Prob Rqst
8214 BI EPM User Experience                     0   2   1   0

Is there an obvious flaw in the code? My best estimation is looking at the coalesce function – which I tried and failed. Among my attempts was this unfriendly piece:

isnull(isnull(isnull(co.group_name, requests.group_Name), incidents.group_Name), problems.group_Name)

but the same result.

Best Answer

Is there an obvious flaw in the code? My best estimation is looking at the coalesce function - ...

Yes to both. One problem is that the 2nd (and 3rd) FULL JOIN use the (derived) tables' group_name columns. As a result, the 2nd and 3rd full join cancel the 1st and the 2nd full join respectively. Your query with only the basic structure, stripped from the details:

select  
    coalesce(co.group_name, requests.group_Name, incidents.group_Name, 
             problems.group_Name) as 'SD Groups'
    ---
from
    ( --- ) as CO
full outer join 
    ( --- ) as Requests
    on co.group_name = requests.group_name
full outer join
    ( --- ) as Problems
    on  requests.group_name = problems.group_name
full outer join 
    ( --- ) as Incidents
    on requests.group_name = incidents.group_name

After the 1st full join:

ON co.group_name = requests.group_name

there are possibly rows from either table (groups or requests) that don't match the other and the empty columns are filled with NULL values.

Then the 2nd full join happens:

ON requests.group_name = problems.group_name

but this ON condition will be satisfied only by rows that have non-null requests.group_name. This basically converts these 2 FULL joins to a complicated mess which is almost (but not exactly) the full join of 3 tables. Some rows will not be matched, In particular the rows from tables co and problems that have the same group_name (but no such row exists in table requests) will not be matched but will end up in different rows.

And again the 3rd full join:

ON requests.group_name = incidents.group_name

will make it even more complicated. As a result, your query is equivalent to the the union of (requests left joined to the 3 other tables) and 3 antijoins (each of the 3 tables with requests):

select  
    ---
from
    ( --- ) as Requests
left outer join
    ( --- ) as CO
    on co.group_name = requests.group_name
left outer join
    ( --- ) as Problems
    on  requests.group_name = problems.group_name
left outer join 
    ( --- ) as Incidents
    on requests.group_name = incidents.group_name

union all 

select  
    ---
from
    ( --- ) as CO
left outer join
    ( --- ) as Requests
    on co.group_name = requests.group_name
where 
    requests.group_name is null

union all 

select  
    ---
from
    ( --- ) as Problems
left outer join
    ( --- ) as Requests
    on problems.group_name = requests.group_name
where 
    requests.group_name is null

union all 

select  
    ---
from
    ( --- ) as Incidents
left outer join
    ( --- ) as Requests
    on incidents.group_name = requests.group_name
where 
    requests.group_name is null

That is the reason for the same group_name appearing in two rows. You can see that this happens only when the CO has no rows with such value but the other tables have.

If you want to really have 3 FULL joins, the ON conditions should be rewritten with either ISNULL or COALESCE:

select  
    coalesce(co.group_name, requests.group_Name, incidents.group_Name, 
             problems.group_Name) as [SD Groups]
    ---
from
    ( ---
    ) as CO
full outer join 
    ( ---
    ) as Requests
    on co.group_name = requests.group_name
full outer join
    ( ---
    ) as Problems
    on coalesce(co.group_name, requests.group_name) 
       = problems.group_name
full outer join 
    ( ---
    ) as Incidents
    on coalesce(co.group_name, requests.group_name, problems_group_name) 
       = incidents.group_name
order by 
    --- ;

Other minor issues is the use of 'single quotes' for the aliases. You should either use the standard "double quotes" or the SQL Server's [square brackets]. Or even better don't quote your identifiers and keep them without spaces and other weird characters. Single quoted strings should be used only for string literals, not for identifiers.

Related Question