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

Question

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.

Look up NOT EXISTS

Eric
– Eric

2020-07-15 21:28:27 +00:00
Commented Jul 15, 2020 at 21:28 — Eric
– Eric, Commented Jul 15, 2020 at 21:28

Gabriele Franco · Accepted Answer · 2020-07-21 14:10:35Z

1

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

edited Jul 21, 2020 at 14:10

answered Jul 15, 2020 at 21:23

Gabriele Franco

9097 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

LucasC922 Over a year ago

Thanks, but this does not return anything.

Gabriele Franco Over a year ago

So all descripts in query1 there are also in query2

Gabriele Franco Over a year ago

I also tested it

LucasC922 Over a year ago

I appreciate it, but I tested it as well and did not get anything. For example, I have a user named Mike and he has only done 1 training out of 35. So the query should show me the 34 training names that he did not complete, but it did not.

Gabriele Franco Over a year ago

I understand now. later I will modify the query as required

|

Eric · Accepted Answer · 2020-07-15 21:31:47Z

1

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
)

answered Jul 15, 2020 at 21:31

Eric

3,2701 gold badge22 silver badges27 bronze badges

3 Comments

LucasC922 Over a year ago

Thanks, but this does not return anything.

Eric Over a year ago

@LucasC922 Doesn't return nothing mean everybody has done all training?

LucasC922 Over a year ago

No, sir. There are a lot of people who have not.

shawnt00 · Accepted Answer · 2020-07-16 14:48:49Z

1

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;

edited Jul 16, 2020 at 14:48

answered Jul 15, 2020 at 22:42

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

5 Comments

LucasC922 Over a year ago

I think this is close, but the query just shows everyone with the list of every training available, including even the ones that they did.

shawnt00 Over a year ago

@LucasC922 The where k.Id is null should eliminate the ones that were actually completed.

shawnt00 Over a year ago

@LucasC922 Then I suspect there's something wrong with the join condition. Do the codes match up exactly?

LucasC922 Over a year ago

I think rather than using JOIN, using EXCEPT works here. I have modified the query in my original question to show it.

shawnt00 Over a year ago

@LucasC922 I looked at your query above. Do the ID and Name columns correspond? That could be the problem with the join. Yes, except in this case.

Collectives™ on Stack Overflow

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

3 Answers 3

7 Comments

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related