August 31, 2006 at 6:59 am
Hi,
I am running a query for a products tables which join to a category table. The problem is that one of the products has two child categories and one parent. Therefore, I get two rows which have exactly the same data but diffrent category descritions. How can I elimate one of these rows?
Sample data: (--> <-- shows the category)
FF100104LG Xtick USB Memory 1GB (with Extension Cable)UB1GVMS01PLG Electronics414.1515.1405-->Flash Cards & Drives<--
FF100104LG Xtick USB Memory 1GB (with Extension Cable)UB1GVMS01PLG Electronics414.1515.1405-->Portable Storage drives and cards<--
Cheers
Reet
August 31, 2006 at 8:56 am
You will need some kind of tiebreaker for the duplicates.
E.g. group by the MIN(category_id) on categories.
Jan
August 31, 2006 at 11:22 am
Two things to do make the columns UNION compatible and create Unique index, UNION will perform an implicit distincts to remove the duplicates in your query result and Unique index will not accept duplicate data if you use the IGNORE_DUP_KEY option. It is not trivial to do comparison with that type of data. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
August 31, 2006 at 11:25 am
If what you are looking for is appending all the possible [Description]s, this should work...
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Products')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.Products
GO
CREATE TABLE dbo.Products( Code varchar(10),
InitialDescription varchar(75),
SomeNumber integer,
SomeOtherNumber varchar(15),
[Description] varchar(35))
GO
--------------------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[GetDescription]') AND xtype IN( N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[GetDescription]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.GetDescription
( @Code varchar(85))
RETURNS varchar(150)
AS
BEGIN
DECLARE @FullRecord varchar(150)
SET @FullRecord = ' '
SELECT @FullRecord = @FullRecord + ' - ' + RTRIM( [Description])
FROM dbo.Products
WHERE Code = @Code
RETURN( @FullRecord)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.Products
VALUES( 'FF100104', 'LG Xtick USB Memory 1GB (with Extension Cable) UB1GVMS01P LG Electronics', 4, '14.15 15.1405', 'Flash Cards & Drives')
INSERT INTO dbo.Products
VALUES( 'FF100104', 'LG Xtick USB Memory 1GB (with Extension Cable) UB1GVMS01P LG Electronics', 4, '14.15 15.1405', 'Portable Storage drives and cards')
SELECT DISTINCT Code + ' ' + InitialDescription + ' ' + CONVERT( varchar, SomeNumber) + SomeOtherNumber + dbo.GetDescription( Code) AS [Single Record]
FROM Products
I wasn't born stupid - I had to study.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply