SP Nightmare - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP Nightmare

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2002-02-25 : 11:42:20

Ok,
How to explain this screw up. I am trying to keep it short as possible but I think the only way to get my problem across is to make the question lenghty and boring.
I have a product table :
TABLE [ProductTable] (
[Product_ID] [nvarchar] (40),
[Manufacturer] [nvarchar] (30) NULL ,
[Product_Name] [nvarchar] (40) NULL ,
[Short] [varchar] (155) NULL ,
[MSRP] [numeric](19, 4) NULL ,
[Price] [numeric](19, 4) NULL ,
[Detailed_Text] [ntext] NULL ,
[Image] [varchar] (50) NULL ,
[Weight] [float] NULL ,
[Inventory] [int] NULL ,
[Taxed] [char] (1) NULL ,
[Accessory] [char] (1) NULL ,
CONSTRAINT [PK_ProductTable] PRIMARY KEY CLUSTERED
([Product_ID]) WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I have a Category Table Where each Product is assigned a Category that it belongs to.

CREATE TABLE [Cat_ProductTable] (
[Cat_ID] [nvarchar] (8) NOT NULL ,
[Sub_Cat_ID] [nvarchar] (8) NOT NULL ,
[Product_ID] [nvarchar] (40) NOT NULL ,
CONSTRAINT [PK_Cat_ProductTable] PRIMARY KEY CLUSTERED
([Cat_ID],[Sub_Cat_ID],[Product_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]

As Well I have a Sub-Category Table where the product belongs to as well.

CREATE TABLE [Sub_CatTable] (
[Cat_ID] [numeric](18, 0) NULL ,
[Sub_Cat_ID] [numeric](18, 0) NOT NULL ,
[Sub_Cat_Name] [varchar] (50) NULL ,
CONSTRAINT [PK_Sub_CatTable] PRIMARY KEY CLUSTERED ([Sub_Cat_ID]) WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY]

And a table that Defines the names of the Categoriy Names.

CREATE TABLE [CatTable] (
[Cat_ID] [numeric](18, 0) NOT NULL ,
[Cat_Name] [varchar] (50) NULL ,
CONSTRAINT [PK_CatTable] PRIMARY KEY CLUSTERED
([Cat_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]

I guess this is turning into a two part question. When I do a query to get all of the information for a Product where I want the info from the Product_ID Table as well as the Category that it belongs to and the Sub-category and the respective names I use this SP :

ALTER PROCEDURE dbo.getproduct
@product_id nvarchar(30)
AS
SELECT ProductTable.*, Cat_ProductTable.Cat_ID, Cat_ProductTable.Sub_Cat_ID
FROM ProductTable
INNER JOIN (Cat_ProductTable
LEFT JOIN (CatTable LEFT JOIN Sub_CatTable On CatTable.Cat_ID = Sub_CatTable.Cat_ID) ON Cat_ProductTable.Cat_ID = CatTable.Cat_ID) ON Cat_ProductTable.Product_ID = ProductTable.Product_ID
WHERE ProductTable.Product_ID = @product_id

I guess my first part of the question is: Am I doing this in the best possible way. Anyone see any problems. A Little explination is at hand. This query is used to view a product on the main HTML page. The Cat ids and Subcats IDs are needed to allow the page to display related products to the main product, as well as Accessories as we will see in the second part of the question.

Now comes the lengthy secon qustion.!!!

I have created a few queries that display the related products to the one referenced above. I have three columns that the HTML will display. Hot Items, Related Items and New Items. As well as Accessories. Actually the best way is to let you take a look at one of the pages, that I am working on. http://ww.critechsystems.com/sc/?ps=p4 is a good example. I am editing this code so if you see an error (Cold Fusion) it is just me.
Now on the right I am displaying three columns Related Products, Hot Items, and New Items. New Items might not make it on th epage all the time but trust me I am working on it. I do not have enough test data to fill the request.

Let me try to explain by showing the tables.

CREATE TABLE [HotItems] (
[Product_ID] [nvarchar] (40) NOT NULL) ON [PRIMARY]
GO

CREATE TABLE [NewItems] (
[Product_ID] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateEntered] [datetime] NULL ,
CONSTRAINT [PK_NewItems] PRIMARY KEY CLUSTERED ([Product_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

Ok STILL WITH ME????

I use this SP to get the related Items. I am sure to get bashed for this one so bash away.

CREATE PROCEDURE dbo.getrelated
(@subcatid numeric(9), @prodid nvarchar(40))
AS
SELECT TOP 15 ProductTable.Product_ID, ProductTable.Image,ProductTable.Product_Name, ProductTable.Short, ProductTable.Price, ProductTable.Manufacturer, CatTable.Cat_ID, CatTable.Cat_Name
FROM ProductTable
INNER JOIN (Cat_ProductTable LEFT JOIN (CatTable LEFT JOIN Sub_CatTable On CatTable.Cat_ID = Sub_CatTable.Cat_ID) ON Cat_ProductTable.Cat_ID = CatTable.Cat_ID) ON Cat_ProductTable.Product_ID = ProductTable.Product_ID
WHERE Sub_CatTable.Sub_Cat_ID = @subcatid AND
ProductTable.Accessory = 'F' AND
ProductTable.Product_ID <> @prodid
ORDER BY NEWID()

I get the hotitems from the following query.

CREATE PROCEDURE dbo.hotitem
AS
SELECT HotItems.Product_ID, Sub_CatTable.Sub_Cat_Name, Sub_CatTable.Sub_Cat_ID, CatTable.Cat_Name, CatTable.Cat_ID, ProductTable.Product_Name,
ProductTable.Price, ProductTable.Image, ProductTable.Short
FROM Sub_CatTable INNER JOIN
ProductTable INNER JOIN
Cat_ProductTable ON ProductTable.Product_ID = Cat_ProductTable.Product_ID INNER JOIN
CatTable ON Cat_ProductTable.Cat_ID = CatTable.Cat_ID ON Sub_CatTable.Sub_Cat_ID = Cat_ProductTable.Sub_Cat_ID RIGHT OUTER JOIN
HotItems ON HotItems.Product_ID = ProductTable.Product_ID
ORDER BY NEWID()

I get the new Items from the following Query.

CREATE PROCEDURE dbo.newitem
AS
SELECT TOP 10 ProductTable.Product_ID, ProductTable.Image,ProductTable.Product_Name, ProductTable.Manufacturer, ProductTable.Price
FROM ProductTable INNER JOIN NewItems ON NewItems.Product_ID = ProductTable.Product_ID
ORDER BY NEWID()

What a mess huh??? Do not be shy I am still learning so bash away.
The first question still remains the same. Any holes in how I am getting the related Product_ID query.

The second problem/question is more complicated, at least to my mind.

When I query the seperate Hot Items / Related Items / New Items there is the possibility of the same product appearing on all three lists. Which is unacceptable.
I am think I need to write a Query that I can pass variables that say three from Related, 5 from Hot Items and 10 from New Items and am returned a list that has such but not related (appearing in the same list).

Any point in the right direction would be greatly appreaciated.
If I am way off base and you want to bash or call me crazy please go ahead, I am at a loss here. I thought I had this all worked out until we noticed the same product appearing multiple times.

THANKS SQL TEAM

Chris
BTW the Reviews and the Accessories HTML are coming from the db as ntext fields as we discussed before.

chrispy
Posting Yak Master

107 Posts

Posted - 2002-02-26 : 11:45:25

OK I guess I confused or was to long in the last post. I am trying to work through this. Maybe if I broke down the question It will be easier to understand.

Currently I have two tables
table ProductTable
(Product_ID, Manufacturer, Product_Name, Price)

AND

table NewItems
(Product_ID)

This should be simple. I want to select all from the ProductTable that are not listed on the NewItems Table.

Once I get this far. Than I have a third table.

tbl HotItems
(Product_ID)

Once I have the above, I then want to select all that are not on the HotiItems list.

To put the query in simple English.
Give me all Product_IDs that are not on the HotItems or the New Items List.

Just looking fo a bump in the right Direction.

Thanks again,
Chris

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-26 : 13:06:30

How about this:

SELECT *
FROM Products
WHERE Product_ID not in (SELECT Product_ID
FROM NewItems
UNION
SELECT Product_ID
FROM HotItems)

HTH
-Chad

chrispy
Posting Yak Master

107 Posts

Posted - 2002-02-26 : 13:50:05

chadmat,
EXCELLENT. Got that much working. I think this helps me on building the rest as well.
Let me see if I can get the rest from this much learned.

Thanks a bunch,
Chris

chrispy
Posting Yak Master

107 Posts

Posted - 2002-02-26 : 14:59:13

Well I have it broken into three different Querires that I call differently :

ALTER PROCEDURE dbo.getrelated
(@subcatid numeric(9), @prodid nvarchar(40))
AS
SELECT ProductTable.Product_ID, ProductTable.Manufacturer, ProductTable.Product_Name, ProductTable.Price
FROM ProductTable INNER JOIN
Cat_ProductTable ON ProductTable.Product_ID = Cat_ProductTable.Product_ID
WHERE (Cat_ProductTable.Sub_Cat_ID = @subcatid)
AND (ProductTable.Accessory = 'F')
AND (ProductTable.Product_ID <> @prodid)

ORDER BY NEWID()

CREATE PROCEDURE dbo.getnewitems
(@subcatid numeric(9), @prodid nvarchar(40))
AS
SELECT ProductTable.Product_ID, ProductTable.Manufacturer, ProductTable.Product_Name, ProductTable.Price
FROM NewItems INNER JOIN
ProductTable ON NewItems.Product_ID = ProductTable.Product_ID
WHERE (NewItems.Product_ID <> 'p4') AND (NewItems.Product_ID NOT IN
(SELECT HotItems.Product_ID
FROM Hotitems
UNION
SELECT ProductTable.Product_ID
FROM ProductTable INNER JOIN
Cat_ProductTable ON ProductTable.Product_ID = Cat_ProductTable.Product_ID INNER JOIN
HotItems ON ProductTable.Product_ID = HotItems.Product_ID
WHERE (ProductTable.Product_ID <> @prodid) AND (Cat_ProductTable.Sub_Cat_ID = @subcatid) AND (ProductTable.Accessory = 'F')))
ORDER BY NEWID()

CREATE PROCEDURE dbo.gethotitems
(@subcatid numeric(9), @prodid nvarchar(40))
AS
SELECT ProductTable.Product_ID, ProductTable.Manufacturer, ProductTable.Product_Name, ProductTable.Price
FROM HotItems INNER JOIN
ProductTable ON HotItems.Product_ID = ProductTable.Product_ID
WHERE (HotItems.Product_ID <> 'p4') AND (HotItems.Product_ID NOT IN
(SELECT NewItems.Product_ID
FROM NewItems
UNION
SELECT ProductTable.Product_ID
FROM ProductTable INNER JOIN
Cat_ProductTable ON ProductTable.Product_ID = Cat_ProductTable.Product_ID INNER JOIN
HotItems ON ProductTable.Product_ID = HotItems.Product_ID
WHERE (ProductTable.Product_ID <> @prodid) AND (Cat_ProductTable.Sub_Cat_ID = @subcatid) AND (ProductTable.Accessory = 'F')))
ORDER BY NEWID()

I call each one and pass it the Product_ID and SUBCAT_ID.

Seems to be working fine. Anyone see any problems with this approach.

Thanks again,
Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources