Queries returning Multiple instances of the same record

I am running SQL 2005 and have created a simple database for interfacing with Visual Web Dev 2005 and Visual Studio 2005. I noticed that my applications where returning multiple instances of the same records in the queries. I went back to the SQL server and created a query, and sure enough, I'm getting each record returned 3 times. Where do I start to resolve this issue?DId you have the records already duplicated in the database tables ? Or did you just made a mistake in your join, defining the wrong joined keys. THe best thing would be to provide some information like DDL and some sample data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

My DB and query are much simpler than what you are imagining:

The DB Structure is:

<MemberID, Int,> - Primary Key Autoincrement

<FirstName, nvarchar(30),>

<LastName, nvarchar(30),>

<Salutation, nvarchar(20),>

<MemberType, nvarchar(20),>

<IsNeighbor, tinyint,>

<Title, nvarchar(30),>

<Address, nvarchar(60),>

<Address2, nvarchar(60),>

<City, nvarchar(30),>

<State, nvarchar(2),>

<Zip, nvarchar(9),>

<Phone, nvarchar(10),>

<Email, nvarchar(50),>

<DateJoined, datetime,>

<ExpirationDate, datetime,>

<SubMemberTo, int,>

<Fax, nvarchar(10),>

<Cellphone, nvarchar(10),>

The SELECT query is:

SELECT [MemberID]

,[FirstName]

,[LastName]

,[Salutation]

,[MemberType]

,[IsNeighbor]

,[Title]

,[Address]

,[Address2]

,[City]

,[State]

,[Zip]

,[Phone]

,[Email]

,[DateJoined]

,[ExpirationDate]

,[SubMemberTo]

,[Fax]

,[Cellphone]

FROM [FriendsSQL].[dbo].[Members]

WHERE FirstName = 'ANNE' and LastName = 'REIS'

Without the WHERE clause the query returns the entire DB without duplication, however, when the WHERE clause is included the output is:

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

Notice that the single record is returned 3 times.

|||DOH! You were right. The records were duplicated. Apparently using the SET Insert Unique ON and not having the Primary Key set allowed the duplications. I've cleaned up the mess and I'll try not to shoot off any more toes. Sorry for the bother. I should have caught that one.