SQL JOIN filter for zero hits in Table2

I have a Client table where some clients have a contract number. We're doing a sanity check to make sure that our Services table has the service that goes with the contract. So, I'm trying to construct a query where the contract field is non-empty, and there is NOT a proper service record to match that client.

Client

id	Contract
1	C01
2	C02
3	C03
4

Services (Where service 1 is the one we're looking for)

id	ClientID	Service
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2
6	3	2
7	3	3
8	4	2
9	4	3

This should only display client 3 as having a contract without having the associated service it's supposed to have.

I have a query that shows me all records in the Services table for clients with contracts and the correct service code. I can't figure out how to find records in the client table that are missing any examples of the record I want in the Services table.

This gets me records WITH the service

SELECT *
  JOIN[System].[dbo].[Services] ON Client.id = Services.ClientID AND Service = 1
  FROM [System].[dbo].[Client]
  WHERE NOT Contract = ''

I've tried JOINing the other direction, but it's still the same issue.

----- Edit ----- The reason I want to only display client 3 because that is the only one with a non-null Contract field where there does not exist in the Services table a record with ClientID=3 and Service=1. There are Service=2 and Service=3, but not Service=1. Clients 1 and 2 both have non-null Contract fields, but they also have a Services record where Service=1. Client 4 has a null Contract field, so it should not have a Services record with Service=1. (I'm not trying to display extraneous Services records with this query. I can build that one on my own.)

Again, this is a sanity check. If we have a contract number then we're supposed to have a record for the matching client with Service=1. We think we have a few where that's not the case. I'm trying to build a query that shows me the problem clients.

I guess in table form, the output for the query I'm trying to build would be:

id	Contract	id	ClientID	Service
3	C03	null	null	null

I'm sure this has been answered elsewhere. But, I can't think of search terms that will find that amongst the mountain of results I've gotten, thus far. I guess generically, this is basically, "How do I query for situations with missing records."

Solution

You were on the right track. You needed a LEFT JOIN to the Services table matching on ClientID and the required Service = 1. You would then add a condition to the WHERE clause that checks for no match with an IS NULL test.

This can also be done using a C.id NOT IN (subselect) condition. A NOT EXISTS() condition can also be used.

Each of the following will identify clients with contracts, but lacking Service = 1.

SELECT C.*
FROM System.dbo.Client C
LEFT JOIN System.dbo.Services S
   ON S.ClientID = C.id
   AND S.Service = 1
WHERE C.Contract > ''
AND S.ClientID IS NULL

SELECT C.*
FROM System.dbo.Client C
WHERE C.Contract > ''
AND C.id NOT IN (
    SELECT S.ClientID
    FROM System.dbo.Services S
    WHERE S.Service = 1
)

SELECT C.*
FROM System.dbo.Client C
WHERE C.Contract > ''
AND NOT EXISTS (
    SELECT *
    FROM System.dbo.Services S
    WHERE S.ClientID = C.id
    AND S.Service = 1
)

The execution plans for each of the above are nearly identical, so you can use the syntax that make the most sense to you.

All yield the following results:

id	Contract
3	C03

See this db<>fiddle for a demo with indexes and query plan.