CODEWITHSUNDEEP
sql-server-2008primary-key
tkvo
tkvo

Reputation: 171

SELECT records with 2 primary keys

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:

Cust:

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'm looking for Cust with no 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 results came out empty. What would be the correct query here since table has composite primary keys?

Upvotes: 4

Views: 2774

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

Tables do not have 2 primary keys. Some have composite primary keys and this seems to be the case here. If the Cust table has (siteid, custid) as the 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 should have one (composite) foreign key, referencing the (composite) 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)
) ;

Then one way to write your query would be (Correction: this is ANSI SQL and works in other 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 pitfall of possible NULL values that will make the NOT IN version to show not expected results (and second because the NOT IN doesn't 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

Upvotes: 4

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

There's more than one way to express your query. Look at the options for specifying clustered and nonclustered primary keys, the execution plans, and ypercube's comments about your tables before you pick one. (I believe that NOT IN isn't sargable.)

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;

Upvotes: 2

Related Questions