Overdrive Overdrive - 3 months ago 14
SQL Question

SQL Server 2012 Compare records and find missing ones based on dynamic locations

I have the following issue, I know how to generally find the missing records from within the same table, except I am not able to know where are the records missing from :

Here is the main table

Location | Role | Subrole
A            | R1    | SR1
A            | R1    | SR2
A            | R1    | SR3
B            | R1    | SR1
B            | R1    | SR2
B            | R1    | SR3
C            | R1    | SR1
C            | R1    | SR2
D            | R1    | SR1


The location A is the master location and all other locations should be compared to A. My end goal is to have this :

MasterLocation | MasterRole | MasterSubrole | Location | Role | Subrole
A        | R1        | SR1          |    B     | R1   | SR1
A        | R1        | SR2          |    B     | R1   | SR2
A        | R1        | SR3          |    B     | R1   | SR3
A        | R1        | SR1          |    C     | R1   | SR1
A        | R1        | SR2          |    C     | R1   | SR2
A        | R1        | SR3          |    C     | R1  | MISSING OR NULL
A        | R1        | SR1          |    D     | R1   | SR1
A        | R1        | SR2          |    D     | R1   | MISSING OR NULL
A        | R1        | SR3          |    D     | R1   | MISSING OR NULL


I have created 2 temp tables

CREATE TABLE #LocA
(
Location Varchar(1),
Role Varchar(2),
SubRole VARCHAR(20)
)

CREATE TABLE #AllOthers
(
Location VARCHAR(1),
Role VARCHAR(2),
SubRole VARCHAR(20)
)

INSERT INTO #LocA
SELECT
Location, Role, SubRole
FROM
TABLE
WHERE
Location = 'A'

INSERT INTO #AllOthers
SELECT
Location, Role, SubRole
FROM
TABLE
WHERE
Location != 'A'

SELECT
A.Location AS MasterLocation,
A.Role AS MasterRole,
A.SubRole AS MasterSubrole
L.Location,
L.Role,
L.Subrole
FROM
#LocA AS A
LEFT JOIN
#Allothers AS L ON A.Role = L.Role
AND A.SubRole = L.Subrole


I am getting

MasterLocation | MasterRole | MasterSubrole | Location | Role|Subrole

A                       | R1              | SR1                 |     B        | R1      | SR1
A                       | R1              | SR2                 |     B        | R1      | SR2
A                       | R1              | SR3                 |     B        | R1      | SR3
A                       | R1              | SR1                 |     C        | R1      | SR1
A                       | R1              | SR2                 |     C        | R1      | SR2
A                       | R1              | SR3                 |     NULL   | NULL   | NULL
A                       | R1              | SR1                 |     D        | R1      | SR1
A                       | R1              | SR2                 |     NULL   | NULL   | NULL
A                       | R1              | SR3                 |     NULL   | NULL   | NULL


So I am actually not sure where the subrole is missing from, C or D. (This table has hundreds of locations.)

Answer

A common use of cross joins is in this type of problem where you need to show all possible combinations. The first half of the query creates that set of values and then the left join attaches the data values with a composite join across the multiple dimensions.

select
    m.Location as MasterLocation,
    m.Role as MasterRole,
    m.Subrole as MasterSubrole,
    l.Location,
    coalesce(t.Role, 'Missing role') as Role,
    coalesce(t.Subrole, 'Missing subrole') as Subrole
from
    T as m
    cross join
    (select distinct Location from T where Location <> 'A') as l
    left outer join T as t
        on     l.Location = t.Location
           and m.Role = t.Role
           and m.Subrole = t.SubRole
where
    m.Location = 'A';

You may prefer to avoid having to mention the literal value A more than once in the query so here's an alternative.

select
    m.Location as MasterLocation,
    m.Role as MasterRole,
    m.Subrole as MasterSubrole,
    l.Location,
    coalesce(t.Role, 'Missing role') as Role,
    coalesce(t.Subrole, 'Missing subrole') as Subrole
from
    (select * from T where Location = 'A') as m
    cross apply
    (select distinct Location from T where Location <> m.Location) as l
    left outer join T as t
        on     l.Location = t.Location
           and m.Role = t.Role
           and m.Subrole = t.SubRole;

If you have nulls in the data and you want to match those up you may want to join this way:

        on     l.Location = t.Location
           and coalesce(m.Role, '!@') = coalesce(t.Role, '!@')
           and coalesce(m.Subrole, '!@#') = coalesce(t.SubRole, '!@#');

http://rextester.com/MNSU54881