SQL view returns same item multiple times

SELECT     DISTINCT dbo.PLFDProducts.ProductID, dbo.PLFDProducts.Product, dbo.PLFDDiscounts.Active, dbo.PLFDDiscounts.ExpirationDate, 
                      dbo.PLFDProducts.Active AS Expr1
FROM         dbo.PLFDProducts INNER JOIN
                      dbo.PLFDProductDiscounts ON dbo.PLFDProducts.ProductID = dbo.PLFDProductDiscounts.ProductID INNER JOIN
                      dbo.PLFDDiscounts ON dbo.PLFDProductDiscounts.DiscountID = dbo.PLFDDiscounts.discountid
WHERE     (dbo.PLFDDiscounts.Active <> N'no') AND (dbo.PLFDDiscounts.ExpirationDate > { fn NOW() }) AND (dbo.PLFDProducts.Active <> N'no')

Select allOpen in new window

DISTINCT would be the obvious choice, though it is quite slow.

SELECT DISTINCT     dbo.PLFDProducts.ProductID, dbo.PLFDProducts.Product, dbo.PLFDDiscounts.Active, dbo.PLFDDiscounts.ExpirationDate, 
                      dbo.PLFDProducts.Active AS Expr1
FROM         dbo.PLFDProducts INNER JOIN
                      dbo.PLFDProductDiscounts ON dbo.PLFDProducts.ProductID = dbo.PLFDProductDiscounts.ProductID INNER JOIN
                      dbo.PLFDDiscounts ON dbo.PLFDProductDiscounts.DiscountID = dbo.PLFDDiscounts.discountid
WHERE     (dbo.PLFDDiscounts.Active <> N'no') AND (dbo.PLFDDiscounts.ExpirationDate > { fn NOW() }) AND (dbo.PLFDProducts.Active <> N'no')

Select allOpen in new window

both of those statements return the same set as without the DISTINCT

I don't know what results your looking for but why not a outer join on some of the tables?  From either what I put here in this window or the window below.

SELECT
dbo.PLFDProducts.ProductID,
dbo.PLFDProducts.Product, dbo.PLFDDiscounts.Active, dbo.PLFDDiscounts.ExpirationDate,
dbo.PLFDProducts.Active AS Expr1

FROM dbo.PLFDProducts
INNER JOIN dbo.PLFDProductDiscounts
ON dbo.PLFDProducts.ProductID = dbo.PLFDProductDiscounts.ProductID
right outer JOIN dbo.PLFDDiscounts
ON dbo.PLFDProductDiscounts.DiscountID = dbo.PLFDDiscounts.discountid
WHERE (dbo.PLFDDiscounts.Active <> N'no')
AND (dbo.PLFDDiscounts.ExpirationDate > { fn NOW() })
AND (dbo.PLFDProducts.Active <> N'no')

SELECT 
dbo.PLFDProducts.ProductID,
dbo.PLFDProducts.Product, dbo.PLFDDiscounts.Active, dbo.PLFDDiscounts.ExpirationDate, 
dbo.PLFDProducts.Active AS Expr1
 
FROM dbo.PLFDProducts 
INNER JOIN dbo.PLFDProductDiscounts
ON dbo.PLFDProducts.ProductID = dbo.PLFDProductDiscounts.ProductID 
Left outer JOIN dbo.PLFDDiscounts
ON dbo.PLFDProductDiscounts.DiscountID = dbo.PLFDDiscounts.discountid
WHERE (dbo.PLFDDiscounts.Active <> N'no') 
AND (dbo.PLFDDiscounts.ExpirationDate > { fn NOW() }) 
AND (dbo.PLFDProducts.Active <> N'no')

Select allOpen in new window

Frequently issues like this come from join conditions returning multiple records for fields that you do not intend.  Are some of these tables one to many relationships?  Maybe you have multiple entries for the same product in your Discounts table in which case if you have multiple active records you are going to return multiple records.  

The query you are looking for, in that case, might involve using an aggregate (Possibly MAX) and a group by clause.  Can you post the specifics of the tables involved?

how do i determine if one to many?
what table specifics do you want to see?

Paste your table structures in here.

Table structure (Column list) would be good to see.  A one to many relationship would mean that there are multiple instances of the same product ID in the discount table, or any other table.  To determine one to many just check the related tables to see if you find multiple instances of a product_id table.  Any table that has a product_id field without a primary key (OR unique index) on the product_id field would be a one to many.

Like this?

USE [**********]
GO
/****** Object:  Table [dbo].[PLFDProducts]    Script Date: 11/12/2008 09:36:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PLFDProducts](
      [Active] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Break1] [int] NULL,
      [Break2] [int] NULL,
      [Break3] [int] NULL,
      [CategoryID] [int] NULL,
      [Description] [nvarchar](3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DietStats] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Image] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Price] [smallmoney] NULL,
      [Price2] [smallmoney] NULL,
      [Price3] [smallmoney] NULL,
      [Product] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ProductID] [int] IDENTITY(1,1) NOT NULL,
      [ProductWeight] [int] NULL,
      [TimeKey] [smalldatetime] NULL,
      [UnitsPerCase] [int] NULL,
      [ServingSuggestions] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ShelfLife] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Yes, that works.  Just add the other tables in your query as well.

USE [****************]
GO
/****** Object:  Table [dbo].[PLFDDiscounts]    Script Date: 11/12/2008 09:38:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PLFDDiscounts](
      [discountid] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Description] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Type] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Amount] [int] NULL,
      [DiscountCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Ceiling] [smallmoney] NULL,
      [Active] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ExpirationDate] [datetime] NULL,
      [ProductSpecific] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Secret] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_PLFDDiscounts_Secret]  DEFAULT (N'no')
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

yeah and the other table

USE [***********]
GO
/****** Object:  Table [dbo].[PLFDProductDiscounts]    Script Date: 11/12/2008 09:39:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PLFDProductDiscounts](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [ProductID] [int] NULL,
      [DiscountID] [int] NULL
) ON [PRIMARY]

Run this query and tell me if you get different results in the discount id field.  I suspect you have multiple discount IDs active causing you to get multiple records.  You should see a unique combination of product IDs / discount IDs.

SELECT     dbo.PLFDProducts.ProductID, dbo.PLFDProducts.Product, dbo.PLFDDiscounts.Active, dbo.PLFDDiscounts.ExpirationDate, dbo.PLFDDiscounts.discountid
                      dbo.PLFDProducts.Active AS Expr1
FROM         dbo.PLFDProducts INNER JOIN
                      dbo.PLFDProductDiscounts ON dbo.PLFDProducts.ProductID = dbo.PLFDProductDiscounts.ProductID INNER JOIN
                      dbo.PLFDDiscounts ON dbo.PLFDProductDiscounts.DiscountID = dbo.PLFDDiscounts.discountid
WHERE     (dbo.PLFDDiscounts.Active <> N'no') AND (dbo.PLFDDiscounts.ExpirationDate > { fn NOW() }) AND (dbo.PLFDProducts.Active <> N'no')

Select allOpen in new window

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.

Sorry, missed a comma. Try this.

SELECT     dbo.PLFDProducts.ProductID, dbo.PLFDProducts.Product, dbo.PLFDDiscounts.Active, dbo.PLFDDiscounts.ExpirationDate, dbo.PLFDDiscounts.discountid,
                      dbo.PLFDProducts.Active AS Expr1
FROM         dbo.PLFDProducts INNER JOIN
                      dbo.PLFDProductDiscounts ON dbo.PLFDProducts.ProductID = dbo.PLFDProductDiscounts.ProductID INNER JOIN
                      dbo.PLFDDiscounts ON dbo.PLFDProductDiscounts.DiscountID = dbo.PLFDDiscounts.discountid
WHERE     (dbo.PLFDDiscounts.Active <> N'no') AND (dbo.PLFDDiscounts.ExpirationDate > { fn NOW() }) AND (dbo.PLFDProducts.Active <> N'no')

Select allOpen in new window

SELECT 
dbo.PLFDProducts.ProductID,
dbo.PLFDProducts.Product, dbo.PLFDDiscounts.Active, dbo.PLFDDiscounts.ExpirationDate, 
dbo.PLFDProducts.Active AS Expr1
 
FROM dbo.PLFDProducts 
LEFT OUTER JOIN dbo.PLFDProductDiscounts
ON dbo.PLFDProducts.ProductID = dbo.PLFDProductDiscounts.ProductID 
RIGHT OUTER JOIN dbo.PLFDDiscounts
ON dbo.PLFDProductDiscounts.DiscountID = dbo.PLFDDiscounts.discountid
WHERE (dbo.PLFDDiscounts.Active <> N'no') 
AND (dbo.PLFDDiscounts.ExpirationDate > { fn NOW() }) 
AND (dbo.PLFDProducts.Active <> N'no')

Select allOpen in new window

same set with multiples

Can you post a small snippet of the result set? Just a couple of the rows that are the same?

here it is

96	Peanut Butter Cookie	yes	2008-11-17 00:00:00.000	72	Yes
94	Muffin Tops Assortment Box	yes	2008-11-17 00:00:00.000	71	Yes
94	Muffin Tops Assortment Box	yes	2008-11-30 00:00:00.000	64	Yes
97	Assorted Cookies	yes	2008-11-17 00:00:00.000	71	Yes
97	Assorted Cookies	yes	2008-11-30 00:00:00.000	64	Yes
85	Muffin Assortment Box	yes	2008-11-30 00:00:00.000	64	Yes

Select allOpen in new window

They are not the same, have different dates.  Use Mathew's suggestion and decide which one you want then set a min or max date

i've got to break off for a while. Work beckons.

Is it allowable to have multiple discounts active for the same product? If so your query is returning the correct results.  If not, well, your query is still returning the right results and you just need to deactivate some of the discounts.

Enjoy work, I will be checking in later.