sql-server - 使用关系表 MS-SQL 筛选表

Question

我需要在 MS-SQL 数据库中过滤具有关系表的表。

我得到:

persId  persName1   catId

 4        Hans        0
 4        Hans        51
 4        Hans        73
 5        Torleif     0
 5        Torleif     5
 5        Torleif    73
 5        Torleif    76
 6        Peter       0
 6        Peter      51
 6        Peter      73
 7        Jonas       0
 7        Jonas      16
 7        Jonas      73

与:

SELECT   distinct  tblPerson.persId
, tblPerson.persName1
, relCatPers.catId
FROM         tblPerson LEFT OUTER JOIN
             relCatPers ON tblPerson.persId = relCatPers.persId

---

但我需要。删除 catId 为 51 的那些。并且名称和 ID 仅出现 1 次:

persId  persName1

  5         Torleif
  7         Jonas

表格:

CREATE TABLE [dbo].[tblPerson](
    [persId] [int] IDENTITY(1,1) NOT NULL,
    [persName1] [varchar](255) NULL,
CONSTRAINT [PK_tblPerson] PRIMARY KEY CLUSTERED 
)


CREATE TABLE [dbo].[relCatPers](
    [rcpId] [int] IDENTITY(1,1) NOT NULL,
    [catId] [int] NOT NULL,
    [persId] [int] NOT NULL,
CONSTRAINT [PK_relCatPers] PRIMARY KEY CLUSTERED
)

Answer 1

试试这个:

Fiddle 1 demo here

select distinct p.persId, p.persName1 
from   tblPerson p left join 
         relCatPers c on p.persId = c.persId
where  p.persId not in
       (select persId from relCatPers where catId = 51)

或者你可以忽略 relCatPers 表并像下面那样做

Fiddle 2 demo here

select p.persId, p.persName1 
from   tblPerson p 
where  p.persId not in
       (select persId from relCatPers where catId = 51)