CODEWITHSUNDEEP
sql-server
LucasC922
LucasC922

Reputation: 169

SQL Server: Compare values from two tables and list missing value

I am working on a report that needs to display people who have not done certain training. I have a query that shows people's names and the raining they've done, and another query that shows the entire list of training.

Query #1:

select distinct a.ID, b.Name, b.Descript, b.Code
from Roster a
join SkillCheckOff b on b.Id = a.ID 
order by a.ID, b.Descript

Query #2:

select Descript, Code
from LookupTable 
where Type = 'TRTSkillCheckOff' 
order by Descript

Final result:

Name          Descript
-----------------------
John          Training A
Abbie         Training A
Mike          Training B
...

Basically, if the descript from query #2 is not found from query #1 for each user, the final query needs to display the name and the missing training. Please help.

Based on the answers provided, I think the below query comes close.

with roster as (
select ID
from Roster
), 
skills as (
select Descript, Code from LookupTable where Type = 'TRTSkillCheckOff'
)
select r.ID, s.Descript
from roster r cross join skills s
EXCEPT
select name, Descript
from  SkillCheckOff

I think using EXCEPT works here.

Upvotes: 0

Views: 322

Answers (3)

Gabriele Franco
Gabriele Franco

Reputation: 899

Below the query with CTE and CROSS JOIN:

;WITH CTE_Table AS (
    SELECT DISTINCT 
        A.ID
        ,B.Name
        ,B.Descript
    FROM
        Roster A
        INNER JOIN SkillCheckOff B ON B.ID = A.ID
)
,CTE_Lookup AS (
    SELECT 
        L.Name
        ,L.Descript
    FROM
        Lookup
    WHERE
        Type = 'TRTSkillCheckOff' 
)                 
SELECT 
    C.ID
    ,C.Name
    ,C.Descript
FROM 
    CTE_Table C
    CROSS JOIN CTE_Lookup L 
WHERE 
    L.Descript <> C.Descript
ORDER BY
    C.ID
    ,C.Descript

Upvotes: 1

shawnt00
shawnt00

Reputation: 17915

You need to generate all pairs from rosters and skills then match that against the check-off list:

with roster as (
    select ID, Name from Roster
), skills as (
    select Descript, Code from LookupTable where Type = 'TRTSkillCheckOff'
)
select r.Name, s.Descript
from roster r cross join skills s
    left outer join SkillCheckOff k on k.Id = r.Id and k.Code = s.Code
where k.Id is null
order by r.Name, s.Descript;

Upvotes: 1

Eric
Eric

Reputation: 3257

You can use NOT EXISTS:

select distinct a.ID, b.Name, b.Descript, b.Code
from Roster a
join SkillCheckOff b on b.Id = a.ID 
WHERE NOT EXISTS (
    select 1
    from LookupTable 
    where Type = 'TRTSkillCheckOff' AND Descript = b.Descript
)

Upvotes: 1

Related Questions