selecting from multiple tables avoiding duplicates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

selecting from multiple tables avoiding duplicates

Author

Topic

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-06 : 11:27:33

Hi

I currently have two tables called Book and JournalPaper, both of which have a column called Publisher. Currently the data in the Publisher column is the Publisher name that is entered straight into either table and has been duplicated in many cases. To tidy this up I have created a new table called Publisher where each entry will have a unique ID.

I now want to remove the Publisher columns from Book and JournalPaper, replace it with an ID foreign key column and move the Publisher name data into the Publisher table. Is there a way I can do this without duplicating the data as some publishers appear several times on both tables?

Any help with this will be greatly appreciated as my limited SQL is not up to this particular challenge!!!
Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-06 : 12:18:25

Sure, don't do that.

Create your publisher table as you suggested, keep the name as the primary key, and then make it a foreign key in your other table.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-07 : 04:00:55

Thanks, but what I need to know is how to construct the SQL statement that will move the data to the Publisher table from the Book and JournalPaper tables without creating duplicates on the Publisher table because much of the data appears on both the Book table and the JournalPaper table.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-07 : 04:12:58

Somthing like this


Insert Publisher
Select Distinct [Name] From 
(
  Select [Name] From Book 
  Union 
  Select [Name] From JournalPaper
) as F 
Where [Name] is Not null

Chirag

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-09-07 : 05:10:11

Just a small point, but the UNION will remove dupes, so you don't need the DISTINCT.

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 05:13:10

And to ensure the Publisher name is not present already,

Insert		Publisher
Select		F.[Name]
From		(
			Select [Name] From Book 
			Union 
			Select [Name] From JournalPaper
		) as F 
Where		F.[Name] is Not null
		And F.[Name] NOT IN (SELECT P.[Name] From Publisher P)

Peter Larsson
Helsingborg, Sweden

obscuregirl
Starting Member

41 Posts

Posted - 2006-09-07 : 05:18:14

Thanks very much for your help! That's worked brilliantly!