SQL Server联接在其他表上不存在的位置 [英] SQL Server join where not exist on other table

问题描述

  + ------------------- + + ------------------- + +--------------------- +|服务||资产||AssetService |+ ------------------- + + ------------------- + + --------------------- +|ID |姓名||ID |姓名||AssetId |服务编号|| ------------------- || ------------------- || --------------------- ||1 |服务1 ||1 |资产1 ||1 |1 ||2 |服务2 ||2 |资产2 ||1 |2 ||3 |服务3 ||3 |资产3 ||2 |2 |+ ------------------- + + ------------------- + |2 |3 |+ --------------------- + 

所以我有这些表.我想获取不在 AssetService 上的 Services ，其中 AssetId = 1 像这样:

  + ------------------- +|服务||ID |姓名|+ ------------------- +|3 |服务3 |+ ------------------- + 

仅通过内部/左侧/右侧连接是否有可能?因为我已经尝试过不同的内部联接组合，但是无法正常工作，例如内部联接Asset a on a.Id！= as.AssetId .我尝试了左右联接.

有人可以帮我吗?

谢谢.

解决方案

我能想到的最简单的方法:

 从服务中选择*不在(选择ServiceId来自AssetService其中AssetId = 1); 

SQLFiddle链接

我认为使用内部连接是不可能的，因为这只会检索符合某些条件的记录，而您正在查找与不匹配匹配的记录./p>

但是，可以通过左连接来实现，如Ctznkane525在他的答案中显示的.

编辑

jarlh 在评论中指出，不在中可能会导致令人惊讶的结果.因此，这是不存在版本:

 选择ID，名称来自Service s不存在的地方(选择 *来自AssetService a其中AssetId = 1和ServiceId = s.Id)； 

SQLFiddle链接

+-------------------+   +-------------------+   +---------------------+
|      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);

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

这篇关于SQL Server联接在其他表上不存在的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！