Cant eliminate duplicate rows from results

This query:

SELECT a.[Name], b.Title, b.ISBN
FROM Authors a
INNER JOIN BookAuthors ba ON a.AuthorID = ba.AuthorID
JOIN Books b ON b.ISBN = ba.ISBN
GROUP BY a.[Name], b.Title, b.ISBN
ORDER BY b.Title

returns 26 rows, however, several of the rows are duplicates, and I can't eliminate them! Arrrgh!

Here's a url to my Database diagram:
Database Diagram Image (http://www.panvox.net/tests/dbpic.jpg)

Any help will be greatly appreciated,

PASIf the Distinct logical operator hasn't addressed the issue (?), consider posting a table ddl / sample data script that reproduces the result).

SELECT Distinct a.[Name], b.Title, b.ISBN
FROM Authors a
INNER JOIN BookAuthors ba ON a.AuthorID = ba.AuthorID
JOIN Books b ON b.ISBN = ba.ISBN
GROUP BY a.[Name], b.Title, b.ISBN
ORDER BY b.Title|||Part of the problem is these tables are not normalized properly!

Anyhow, here's the .sql file for the tables involved,

PAS

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BookAuthors_Authors]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[BookAuthors] DROP CONSTRAINT FK_BookAuthors_Authors
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BookAuthors_Books]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[BookAuthors] DROP CONSTRAINT FK_BookAuthors_Books
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_OrderItems_Books]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[OrderItems] DROP CONSTRAINT FK_OrderItems_Books
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Authors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Authors]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BookAuthors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BookAuthors]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Books]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Books]
GO

CREATE TABLE [dbo].[Authors] (
[AuthorID] [int] NOT NULL ,
[Name] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BookAuthors] (
[BookAuthorID] [int] NOT NULL ,
[ISBN] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AuthorID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Books] (
[ISBN] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Publisher] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UnitPrice] [money] NOT NULL ,
[Abstract] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Pages] [smallint] NOT NULL ,
[Published] [datetime] NOT NULL ,
[Stock] [int] NOT NULL
) ON [PRIMARY]
GO|||RE:
Part of the problem is these tables are not normalized properly!

Much easier to see the ddl than the diagram; "not normalized properly" almost always becomes an issue. (Same ISBN, multiple different pub dates, etc., etc., no doubt.) In that case, normalize appropriately, or :( insert the duplicate rows result set into #worktable and use the Distinct logical operator on the #worktable to return a de-duplicated result set.

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
Ragnar
One method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>
|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
RagnarOne method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
RagnarOne method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I have a table in an SQL Server 2000 table that has no primary key, in
>> fact no indexes at all. (I didn't design this (:-)) )
>> I found that there is a duplicate row in the table but when I try to
>> delete it I get an error message:
>> "Key column information insufficient or incomplete. Too many rows were
>> affected by this update."
>> I had tried adding a primary key index but of course I couldn't since
>> there is a duplicate row.
>> I also tried just opening the table in EM and editing the data in the
>> column I want as primary key for one of the duplicate rows so I could set
>> the primary key, but I got an error message that the transaction can't
>> start in firehose mode.
>> Is there a simple way I can get out of this catch22 box? The DB supports
>> a busy Web site, so I was hoping to avoid shutting down the site.
>> Any help would be appreciated.
>> Ragnar
>