How to Anti Join in MSSQL when the join depends on data from a 3rd table

Question

Here is my situation.

PayTable
+-------+------+--------------+
| Craft | Job  | sequence     |
+-------+------+--------------+
| 400   | 1    | 1            |
+-------+------+--------------+
| 401   | 2    | 2            |
+-------+------+--------------+
| 5501  | 2    | 3            |
+-------+------+--------------+

Jobs
+-------+------+------+
| Job | CraftTemplate |
+-------+------+------+
| 1   | 1             |
+-------+------+------+
| 2   | 1             |
+-------+------+------+


Pay Template
+-------+--+
| Template |
+-------+--+
| 1        |
+-------+--+

PayCraftTemplate
+-------+------+---------+
| PayTemplate    | Craft |
+-------+------+---------+
| 1              | 400   |
+-------+------+---------+
| 1              | 401   |
+-------+------+---------+

What I need to do is find all crafts from the PayTable where the craft does not exist in the PayCraftTemplate. This seems pretty straight forward as an anti join pattern, but I cannot seem to get the data to return back appropriately.

The Join Links are:

PayTable INNER JOIN Jobs by Job -> Job
Jobs LEFT OUTER JOIN Pay Template by CraftTemplate -> Template
Pay Template LEFT OUTER JOIN by Template -> PayTemplate

Here is my current attempt:

select
    *
FROM
    PayTable
WHERE NOT EXISTS (
    SELECT 1
        FROM
    Jobs 
        LEFT OUTER JOIN PayTemplate
        ON PayTemplate.Template = Jobs.CraftTemplate
    LEFT OUTER JOIN PayCraftTemplate 
        ON PayCraftTemplate.Template = PayTemplate.Template
    WHERE 
        PayTable.Craft = PayCraftTemplate.Craft AND PayTable.Job = Jobs.Job
) AND PayTable.Job IS NOT NULL AND PayTable.Craft IS NOT NULL

This is not returning the data I expect, I would expect row 3 of PayTable to only return instead I am getting rows 1,2

SqlZim · Accepted Answer · 2017-02-17T14:18:24.493

1

I'm guessing you moved some things around to post the question and basically fixed it. From the above query, you just need to change PayCraftTemplate.Template to PayCraftTemplate.PayTemplate

At first I thought this was an issue with left join with a where unintentionally turning it into an inner join, but in this case inner join is what is needed anyhow, so that wasn't the issue.

select    *
from  PayTable
where not exists (
  select 1
  from Jobs 
    inner join PayTemplate
      on PayTemplate.Template = Jobs.CraftTemplate
      and Jobs.Job = PayTable.Job
    inner join PayCraftTemplate 
      on PayCraftTemplate.PayTemplate = PayTemplate.Template
     and PayCraftTemplate.Craft  = PayTable.Craft 
  )
  and PayTable.Job is not null
  and PayTable.Craft is not null

results:

+-------+-----+----------+
| Craft | Job | sequence |
+-------+-----+----------+
|  5501 |   2 |        3 |
+-------+-----+----------+

edited Feb 17 '17 at 14:18

answered Feb 17 '17 at 14:00

SqlZim

37,248
6
41
59

Why do you link PayTable in the inner joins instead of doing a where clause on the inner sub select? – Jeremy Keczan Feb 17 '17 at 14:16
That's left over from when I was moving the where clause to the joins because it was a `left join`. They can go back to the `where` clause. I just finished editing my answer, showing how your answer works with the typo fixed. – SqlZim Feb 17 '17 at 14:19
@JeremyKeczan Yep! Very close! – SqlZim Feb 17 '17 at 14:25

How to Anti Join in MSSQL when the join depends on data from a 3rd table

1 Answers1