Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39

    Unanswered: Can't 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

    Any help will be greatly appreciated,

    PAS
    Last edited by phpPete; 01-22-03 at 00:17.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Question Re: Can't eliminate duplicate rows from results

    If 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

  3. #3
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39
    Part of the problem is these tables are not normalized properly!

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

    PAS

    Code:
    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

  4. #4
    Join Date
    Oct 2002
    Posts
    369
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •