CODEWITHSUNDEEP
sqlsql-serversql-server-2008
The_DemoCorgin
The_DemoCorgin

Reputation: 744

Deleting Duplicate Rows in VIew in SQL Server 2008

I have two tables, TABLE1 and TABLE2. TABLE1 Has 4 columns: Name, Client, Position, and ID. TABLE2 has 3 columns: Amount, Time, and ID. For every ID in TABLE1 there are one or more entries in TABLE2, all with identical ID and Time values but varying Amount values.

In a view, I have a concatenated string of Name, Client, Position, and ID from TABLE1, but I also need to concatenate the Time for each ID from TABLE2 to this string. If I do a join as I create the view, I get a ton of duplicate strings in the view as it lists the same ID multiple times for each value of Amount in TABLE2.

I need to get rid of the duplicates, so I either need to avoid the replication that occurs from the join, or find a way to simply delete all the duplicates from the view.

Hopefully this is all clear enough. Thank you for reading and for any help you can provide!

Upvotes: 0

Views: 787

Answers (1)

Andrew Savinykh
Andrew Savinykh

Reputation: 26300

DISTINCT could be appropriate:

CREATE VIEW [dbo].[View1]
AS
SELECT distinct 
  dbo.Table1.Id, 
  dbo.Table1.Name, 
  dbo.Table1.Client,
  dbo.Table1.Position, 
  dbo.Table2.[Time]
FROM dbo.Table1 
LEFT OUTER JOIN dbo.Table2 
ON dbo.Table1.Id = dbo.Table2.Id

SqlFiddle: http://sqlfiddle.com/#!3/65651/1

On a side note, depending on what your table structures really are, you might consider having a surrogate primary key on the table2 if you don't have a natural one. I have not put one in the example, because chances are that you already have one - just making sure.

Upvotes: 1

Related Questions