sql server programming: Records in table 1 but not table 2 with a composite primary key -- sql server programming

Re: Records in table 1 but not table 2 with a composite primary key

Roy Harvey
1/24/2007 8:38:59 AM

sql server programming: I generally code these using NOT EXISTS, as un Uri's example. However,
you said you had problems trying to use OUTER JOIN. Here is an
example using that approach.

SELECT Names2.*
FROM Names2
LEFT OUTER
JOIN Names1
ON Names2.First_Name = Names1.First_Name
AND Names2.Middle_Name = Names1.Middle_Name
AND Names2.Last_Name = Names1.Last_Name
WHERE Names1.Last_Name IS NULL

Roy Harvey
Beacon Falls, CT

On Wed, 24 Jan 2007 22:48:34 +1300, "Martin"
[quoted text, click to view]

Re: Records in table 1 but not table 2 with a composite primary key

Uri Dimant
1/24/2007 11:50:18 AM

Martin

SELECT * FROM names2 WHERE NOT EXISTS (SELECT * FROM names1
WHERE names2. First_name=names1.First_name AND names2. middle_name
=names1.middle_name
AND names2. last_name =names1.last_name )

[quoted text, click to view]

Records in table 1 but not table 2 with a composite primary key

Martin
1/24/2007 10:48:34 PM

Hi,

I have a problem where I must get all records from "names2" that are not in
"names1" table.
both tables are identical instructure.
Both have a composite primary key on three columns , First_name ,
middle_name , last_name.

This query is proving to be troublesome due to the composite primary key.
normally i would just do a right outer join, however this does not seem to
work the same way
for a composite primary key as it does for say a single row (auto number
primary key)

could anybody please help me out or point me in the right direction.

I have include a script below so that the problem can be re-produced.

many thanks in advance.

cheers

martin.

============================================================================
==============

USE [MASTER]

GO

CREATE DATABASE [TEST]

GO

USE [TEST]

GO

/****** Object: Table [dbo].[Names1] Script Date: 01/24/2007 22:16:05
******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Names1](

[First_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Middle_Name] [varchar](50) NOT NULL,

[Last_Name] [varchar](50) NOT NULL,

[Date_of_Birth] [datetime] NOT NULL,

CONSTRAINT [PK_Names1] PRIMARY KEY CLUSTERED

(

[First_Name] ASC,

[Middle_Name] ASC,

[Last_Name] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[Names2] Script Date: 01/24/2007 22:16:05
******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Names2](

[First_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Middle_Name] [varchar](50) NOT NULL,

[Last_Name] [varchar](50) NOT NULL,

[Date_of_Birth] [datetime] NOT NULL,

CONSTRAINT [PK_Names2] PRIMARY KEY CLUSTERED

(

[First_Name] ASC,

[Middle_Name] ASC,

[Last_Name] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

INSERT INTO [dbo].[Names1] values('John','','doe1',getdate())

INSERT INTO [dbo].[Names1] values('John','','doe2',getdate())

INSERT INTO [dbo].[Names1] values('John','','doe3',getdate())

INSERT INTO [dbo].[Names1] values('John','','doe4',getdate())

INSERT INTO [dbo].[Names1] values('John','','doe5',getdate())

INSERT INTO [dbo].[Names2] values('John','','doe1',getdate())

INSERT INTO [dbo].[Names2] values('John','','doe2',getdate())

INSERT INTO [dbo].[Names2] values('John','','doe3',getdate())

INSERT INTO [dbo].[Names2] values('John','','doe4',getdate())

INSERT INTO [dbo].[Names2] values('John','','doe5',getdate())

INSERT INTO [dbo].[Names2] values('John','','doe6',getdate())

INSERT INTO [dbo].[Names2] values('John','','doe7',getdate())

INSERT INTO [dbo].[Names2] values('John','','doe8',getdate())

INSERT INTO [dbo].[Names2] values('John','','doe9',getdate())

Re: Records in table 1 but not table 2 with a composite primary key

Martin
1/25/2007 7:07:41 AM

Thanks a lot Uri and Roy.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.