May 10, 2002 at 8:49 am
Table:
CREATE TABLE [Products] (
[productid] [int] NOT NULL ,
[sku] [varchar] (50) AS NULL ,
[shortsku] [int] NULL ,
[edp] [nchar] (10) AS NULL ,
[type] [varchar] (4) AS NULL ,
[category] [varchar] (2) AS NULL ,
[name] [varchar] (100) AS NULL ,
[title] [varchar] (200) AS NULL ,
[heading] [varchar] (100) AS NULL ,
[subheading] [varchar] (100) AS NULL ,
[regularprice] [nvarchar] (10) AS NULL ,
[saleprice] [numeric](19, 4) NULL ,
[clearanceprice] [numeric](19, 0) NULL ,
[productdescription] [varchar] (2500) AS NULL ,
[keywords] [varchar] (250) AS NULL ,
[imagepath] [varchar] (200) AS NULL ,
[varchar] (50) AS NULL ,
[productsize] [varchar] (50) AS NULL ,
[width] [varchar] (50) AS NULL ,
[inventorycount] [char] (5) AS NULL ,
[availdate] [smalldatetime] NULL ,
[modifydate] [datetime] NULL
) ON [PRIMARY]
GO
Problem:
the shortsku field has duplicates. I need all fields but with distinct shortsku.
thanks
chris
</cm>
May 10, 2002 at 9:16 am
Are you saying for select or for input you only wnt uniqeus allowed?
If for insert then change like so.
CREATE TABLE [Products] (
[productid] [int] PRIMARY KEY NOT NULL ,
[sku] [varchar] (50) NULL ,
[shortsku] [int] NULL ,
[edp] [nchar] (10) NULL ,
[type] [varchar] (4) NULL ,
[category] [varchar] (2) NULL ,
[name] [varchar] (100) NULL ,
[title] [varchar] (200) NULL ,
[heading] [varchar] (100) NULL ,
[subheading] [varchar] (100) NULL ,
[regularprice] [nvarchar] (10) NULL ,
[saleprice] [numeric](19, 4) NULL ,
[clearanceprice] [numeric](19, 0) NULL ,
[productdescription] [varchar] (2500) NULL ,
[keywords] [varchar] (250) NULL ,
[imagepath] [varchar] (200) NULL ,
[varchar] (50) NULL ,
[productsize] [varchar] (50) NULL ,
[width] [varchar] (50) NULL ,
[inventorycount] [char] (5) NULL ,
[availdate] [smalldatetime] NULL ,
[modifydate] [datetime] NULL ,
CONSTRAINT [IX_Products] UNIQUE NONCLUSTERED
(
[shortsku]
) ON [PRIMARY]
) ON [PRIMARY]
GO
However you can only have one NULL in the column otherwise would not be a uniqeu NULL.
If you want to have multiple NULLs
Then keep your current table and ad a trigger for insert and update like so.
CREATE TRIGGER tr_Uni_shortsku_Inst ON dbo.Products
FOR INSERT
AS
IF (SELECT COUNT(*) FROM dbo.Products WHERE shortsku = (SELECT shortsku FROM inserted)) > 1
RAISERROR ('You cannot insert a duplicate shortsku.',16,-1)
RETURN
CREATE TRIGGER tr_Uni_shortsku_Upd ON dbo.Products
FOR UPDATE
AS
IF (SELECT COUNT(*) FROM dbo.Products WHERE shortsku = (SELECT shortsku FROM inserted)) > 1
RAISERROR ('You cannot insert a duplicate shortsku.',16,-1)
RETURN
Of Course these only take into account a single record insert or update. You would need to use something to check each record if multiple inserts but this should give you an idea of what to do.
If for Select then you are getting distinct values as each is an entity.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 10, 2002 at 11:13 am
Sorry, I meant for Select. For example, if i have three records each with a shortsku value of 10006, i want to be able to select only one of the records.
i thought this would work
CREATE Procedure GetProducts
As
Select distinct shortsku
into #tempproducts
From Products
Order By shortsku
SELECT #tempproducts.shortsku,products.shorsku,
products.[name],products.title,products.heading,
products.subheading,products.regularprice,products.productdescription,products.imagepath
From #tempproducts LEFT JOIN Products ON #tempproducts.shortsku = Products.shortsku
GO
but it doesnt.
thanks for your help.
chris
</cm>
May 10, 2002 at 11:46 am
Couple of different possible ways.
SELECT
products.shorsku,
products.[name],
products.title,
products.heading,
products.subheading,
products.regularprice,
products.productdescription,
products.imagepath
FROM
Products
WHERE
productid IN (SELECT MAX(productid) FROM Products isub WHERE isub.shorsku = Products.shorsku)
Should do the trick by giving the highest productid number for each shorsku, so you get a single shorsku record back.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 10, 2002 at 12:00 pm
works great. thanks alot
</cm>
May 10, 2002 at 12:33 pm
Thanks for the replies.
Productid is unique. I will always be returning multiple records. Maybe for production i should just create another table [not temp] and query off that? is that the performance problem with your procedure?
thanks
</cm>
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply