+-------------------+ +-------------------+ +---------------------+
| 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.
The simplest I can think of:
select * from Service
where Id not in (
select ServiceId
from AssetService
where AssetId = 1);
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.
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);