SQL Server join where not exist on other table

Question

+-------------------+   +-------------------+   +---------------------+
|      Service      |   |       Asset       |   |     AssetService    |
+-------------------+   +-------------------+   +---------------------+
| Id   |    Name    |   | Id   |    Name    |   | AssetId | ServiceId |
|-------------------|   |-------------------|   |---------------------|
| 1    |  Service 1 |   | 1    |   Asset 1  |   |     1   |     1     |
| 2    |  Service 2 |   | 2    |   Asset 2  |   |     1   |     2     |
| 3    |  Service 3 |   | 3    |   Asset 3  |   |     2   |     2     |
+-------------------+   +-------------------+   |     2   |     3     |
                                                +---------------------+

So I have these tables. I want to get the Services that is not on AssetService where AssetId = 1 Like this:

+-------------------+
|      Service      |
| Id   |    Name    |
+-------------------+
| 3    |  Service 3 |
+-------------------+

Is this possible with just inner/left/right join? because I already tried different combinations of inner join but it's not working, like this inner join Asset a on a.Id != as.AssetId. I event tried left and right join.

Can somebody help me?

Thanks.

Answer 1

You can you use an intelligent left join to return non-matching rows only from left table(Service)

SELECT S.Id, S.Name FROM [Service] S
LEFT JOIN ServiceAsset SA
ON S.Id = SA.ServiceId
WHERE SA.ServiceId IS NULL

Note: INNER JOIN returns the matching rows whereas you want the non matching rows then use LEFT JOIN instead

Answer 2

The simplest I can think of:

select * from Service
where Id not in (
    select ServiceId
    from AssetService 
    where AssetId = 1);

SQLFiddle link

I don't think it's possible using inner join, because that would only retrieve records that match some criteria and you are looking for records that do not match.

It is, however, possible to do it with left join as Ctznkane525 shows in his answer.

Edit

As jarlh pointed out in the comments, not in might lead to surprising results when there are nulls in the subquery. So, here is the not exists version:

select Id, Name
from Service s
where not exists (
    select *
    from AssetService a
    where AssetId = 1
    and ServiceId = s.Id);

SQLFiddle link

Answer 3

Try this:

select * from Service where Id not in (
    select ServiceId from AssetService where AssetId = 1 
    -- we have to filter out NULLs, in case of NULL values query result will be empty
    and ServiceId not null
)

It doesn't require any join.

Here is solution with join:

select Id, Name from Service
except
select S.Id, S.Name from Service S join AssetService [AS] on S.Id = [AS].ServiceId
where [AS].AssetId = 1