[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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

0
webdork
Asked:
webdork
  • 9
  • 8
  • 7
  • +1
2 Solutions
 
Nathan RileyFounder/CTOCommented:

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
 
webdorkAuthor Commented:
both of those statements return the same set as without the DISTINCT
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Nathan RileyFounder/CTOCommented:
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 RileyFounder/CTOCommented:
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 RileyFounder/CTOCommented:
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 RileyFounder/CTOCommented:

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
 
matthewrhoadesCommented:
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 RileyFounder/CTOCommented:
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 RileyFounder/CTOCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 8
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now