SQL Server join where not exist on other table

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] SLEFT JOIN ServiceAsset SAON S.Id = SA.ServiceIdWHERE SA.ServiceId IS NULL

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

The simplest I can think of:

select * from Servicewhere 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, Namefrom Service swhere not exists (    select *    from AssetService a    where AssetId = 1    and ServiceId = s.Id);

SQLFiddle link

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 Serviceexceptselect S.Id, S.Name from Service S join AssetService [AS] on S.Id = [AS].ServiceIdwhere [AS].AssetId = 1