SELECT records with two primary keys

Question

I have 2 tables:

Cust TABLE:

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

Children TABLE:

 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) 

values:

Custom:

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

Children:

 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 

I am looking for Cust without children, and here is my query:

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

But the results were empty. What will be the correct query here since the table has composite primary keys?

Answer 1

There are no 2 primary keys in the tables. Some have composite primary keys, and this seems to be the case here. If the Cust table has (siteid, custid) as a PRIMARY KEY :

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

Then your foreign key definitions are wrong. You must have one (composite) foreign key referencing the primary key (composite):

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

Then one way to write your query would be (Bugfix: this is ANSI SQL and works in another DBMS, but not in SQL-Server):

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

Or better with NOT EXISTS because it avoids the possible NULL errors that will make the NOT IN version display the expected results (and secondly, since NOT IN does not work 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 ) ; 

Tested in SQL-Fiddle

Answer 2

There is more than one way to express your request. See options for specifying clustered and nonclustered primary keys, yccube execution plans, and comments about your tables before you select them. (I believe NOT IN not amenable.)

 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;