ВЫБЕРИТЕ записи с 2 первичными ключами

Question

У меня есть 2 таблицы:

Cust TABLE:

siteid nvarchar(2) PRIMARY KEY,
custid int PRIMARY KEY,
fname varchar(30),
lname varchar(30)

Детский стол:

childid1 nvarchar(2) PRIMARY KEY,
childid2 int PRIMARY KEY,
siteid nvarchar(2),
custid int,
lname varchar(30),
lname varchar(30),
FOREIGN KEY(siteid)REFERENCES Cust(siteid),
FOREIGN KEY(custid)REFERENCES Cust(custid)

Ценности:

Каст:

siteid | custid | fname | lname
A1     | 1111   | John  | S
A2     | 1111   | Steve | H
B1     | 2222   | Paul  | N
C3     | 3333   | Mary  | J

Дети:

childid1 | childid2 | siteid | custid | fname | lname
A6       | 1010     | A1     | 1111   | Lisa  | S
A8       | 1011     | A1     | 1111   | Linda | S
A9       | 1012     | A1     | 1111   | Jose  | S
D9       | 1013     | A2     | 1111   | Jake  | H
D1       | 1014     | B1     | 2222   | Judy  | N
D1       | 1015     | B1     | 2222   | Judy  | N

Я ищу Cust без детей, и вот мой запрос:

SELECT * FROM Cust WHERE Cust.siteid NOT IN(
SELECT Children.siteid FROM Children
) AND Cust.custid NOT IN(
SELECT Children.custid FROM Children
)

Но результаты оказались пустыми. Какой будет правильный запрос, поскольку таблица имеет составные первичные ключи?

Answer 1

Таблицы не имеют 2 первичных ключей. У некоторых есть составные первичные ключи, и это, кажется, имеет место здесь. Если Cust стол имеет (siteid, custid) как PRIMARY KEY:

CREATE TABLE Cust
( siteid nvarchar(2),
  custid int,
  fname varchar(30),
  lname varchar(30),
  PRIMARY KEY (siteid, custid)              -- one primary key
) ;

Тогда ваши определения внешнего ключа неверны. У вас должен быть один (составной) внешний ключ, ссылающийся на (составной) первичный ключ:

CREATE TABLE Children
( childid1 nvarchar(2),
  childid2 int,
  siteid nvarchar(2),
  custid int,
  fname varchar(30),
  lname varchar(30),
  PRIMARY KEY (childid1, childid2),
  FOREIGN KEY (siteid, custid)               -- one foreign key 
    REFERENCES Cust(siteid, custid)
) ;

Тогда одним из способов написания вашего запроса будет (Исправление: это ANSI SQL и работает в других СУБД, но не в SQL-Server):

SELECT * 
FROM Cust 
WHERE (siteid, custid) NOT IN
      ( SELECT siteid, custid  
        FROM Children
      ) ;

Или лучше с NOT EXISTS потому что это избегает ловушки возможного NULL значения, которые сделают NOT IN версия для отображения непредвиденных результатов (и вторая, потому что NOT IN не работает в SQL-Server:)

SELECT * 
FROM Cust AS c
WHERE NOT EXISTS
      ( SELECT * 
        FROM Children AS ch
        WHERE ch.siteid = c.siteid
          AND ch.custid = c.custid
      ) ;

Протестировано в SQL-Fiddle

Answer 2

Существует более одного способа выразить ваш запрос. Посмотрите варианты для указания кластерных и некластеризованных первичных ключей, планов выполнения и комментариев ypercube о ваших таблицах, прежде чем выбрать одну. (Я полагаю, что NOT IN не прощальный.)

select cust.siteid, cust.custid
from cust
left join children
       on cust.siteid = children.siteid
      and cust.custid = children.custid
where children.siteid is null;