SQL view returns same item multiple times

How can i prevent the attached statement from returning the same item multiple times?
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 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')

Open in new window

webdorkAsked:
Who is Participating?
 
matthewrhoadesConnect With a Mentor Commented:
Try this:  (Modified version of Gallitin's query)
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 
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')

Open in new window

0
 
Nathan RileyFounderCommented:

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')

Open in new window

0
 
OBonioCommented:
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')

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
webdorkAuthor Commented:
both of those statements return the same set as without the DISTINCT
0
 
Nathan RileyFounderCommented:
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')

Open in new window

0
 
matthewrhoadesCommented:
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?
0
 
webdorkAuthor Commented:
how do i determine if one to many?
what table specifics do you want to see?
0
 
Nathan RileyFounderCommented:
Paste your table structures in here.
0
 
matthewrhoadesCommented:
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.
0
 
webdorkAuthor Commented:
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]
0
 
matthewrhoadesCommented:
Yes, that works.  Just add the other tables in your query as well.
0
 
webdorkAuthor Commented:
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
0
 
Nathan RileyFounderCommented:
yeah and the other table
0
 
webdorkAuthor Commented:
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]
0
 
matthewrhoadesCommented:
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')

Open in new window

0
 
webdorkAuthor Commented:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
0
 
matthewrhoadesCommented:
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')

Open in new window

0
 
Nathan RileyFounderCommented:

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')

Open in new window

0
 
webdorkAuthor Commented:
same set with multiples
0
 
matthewrhoadesCommented:
Can you post a small snippet of the result set? Just a couple of the rows that are the same?
0
 
webdorkAuthor Commented:
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

Open in new window

0
 
Nathan RileyFounderCommented:
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
0
 
Nathan RileyConnect With a Mentor FounderCommented:
Here possibly
SELECT 
dbo.PLFDProducts.ProductID,
dbo.PLFDProducts.Product, 
dbo.PLFDDiscounts.Active, 
max(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')
GROUP BY dbo.PLFDProducts.ProductID, dbo.PLFDProducts.Product, 
dbo.PLFDDiscounts.Active, dbo.PLFDProducts.Active

Open in new window

0
 
webdorkAuthor Commented:
i've got to break off for a while. Work beckons.

0
 
matthewrhoadesCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.