Product Product Options
-
I have a products table that have options. Now these options are just product that can come from the same table.
Here is my schema that I have so that I can get a many relationship to other products.
Here is my code
ALTER proc [dbo].[uspUtbProduct_Select_W_Top1Photo_4Opts]
@SubCategoryID INT,
@ProductID INT
AS
DECLARE @OptionsTable TABLE
(
ProductID INT,
SubCategoryID INT,
ProductName VARCHAR(30),
ProductNumber VARCHAR(30),
PhotoID INT,
PhotoName VARCHAR(30)
)
INSERT INTO @OptionsTable
SELECT pr.ProductID,
------------------------------
-- prPo.ProductOptionID,
------------------------------
pr.SubCategoryID,
pr.Name AS 'ProductName',
pr.Number AS 'ProductNumber'
------------------------------
,ph.[PhotoID]
,ph.[Name] AS 'PhotoName'
FROM Production.utbProduct pr WITH(NOLOCK)
left outer join (select ProductId, max(PhotoId) as PhotoId
from Production.utbProductUtbPhoto WITH(NOLOCK)
group by ProductId) as prph
on pr.ProductId = prph.ProductId
left outer join Files.utbPhoto ph
on prph.PhotoId = ph.PhotoId
left join Production.utbProductUtbProductOption prpo
on prpo.ProductID = pr.ProductID
WHERE pr.SubCategoryID = @SubCategoryID
AND pr.ProductID <> @ProductID
SELECT * FROM @OptionsTable t1
-- WHERE
-- (SELECT ProductOptionID,ProductID FROM Production.utbProductUtbProductOption
-- WHERE ProductID != @ProductID)t2
exec [dbo].[uspUtbProduct_Select_W_Top1Photo_4Opts]2,7
--
What I am trying to do is when the user adds product options to their product I would like to NO LONGER display the products that they have chosen for the options. However, I either get no results are I get undesired results.
Can someone show me how to only return the product options that do not already exists in the utbProductUtbProductOption table?
Thanks
Erik
Dam again!
-
I need to know more about how you plan to keep track of what options have been selected. Are you updating any permnanent table with this information? Since you indicated a "user" having selected options, can I presume this is a web-based order process? From the code you provided, I can't see a clear picture of where the already selected options are being kept, so until we know where this information resides, it would be impractical to try and write a query.
Also, a table variable you declare in a query goes away when that query is complete, and does not hang around for re-use in a subsequent call to that query code, so perhaps you need to make that something mroe permanent? (e.g., an array variable in your asp page).
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thanks for the reply grasshopper !
What this is, is a product management system, and not the order system.
What I need to do while managing the products is do a insert into Production.utbProductUtbProductOption
The code below shows how I do inserts and updates on the Production.utbProductUtbProductOption table
---> Pass a list of products(Which become a product's options) in the sproc below
ALTER PROC [dbo].[uspUtbProduct_Options_Update_Ext]
@List XML,
@ProductID INT,
@UpdateFalse0InsertTrue1 BIT
AS
SET NOCOUNT ON;
DECLARE @ProductOptionIDs INT, @Cnt INT, @ProductOptionID INT;
DECLARE @TableVar table
(ID int identity(1,1) PRIMARY KEY,
ProductID INT NOT NULL
)
INSERT INTO @TableVar(ProductID)
SELECT ProductID FROM [SplitList](@List);
SELECT @Cnt = LEN(ProductID) FROM [SplitList](@List)
WHILE(@Cnt > 0)
BEGIN
SELECT @ProductOptionID = ProductID FROM @TableVar WHERE ID = (@Cnt);
IF(@UpdateFalse0InsertTrue1 = 0)
BEGIN
UPDATE Production.utbProductUtbProductOption
SET ProductOptionID = @ProductOptionID
WHERE Production.utbProductUtbProductOption.ProductID = @ProductID
END
IF(@UpdateFalse0InsertTrue1 = 1)
BEGIN
INSERT INTO [Production].[utbProductUtbProductOption]
([ProductID]
,[ProductOptionID])
VALUES
(@ProductID
,@ProductOptionID)
END
SET @Cnt = @Cnt-1
END
SET NOCOUNT OFF;
Here is where I unwrap the list
ALTER FUNCTION [dbo].[SplitList]
(
@list AS XML
)
RETURNS TABLE
AS
RETURN
(
SELECT tempTable.item.value('.', 'VARCHAR(MAX)') AS ProductID
FROM @list.nodes('list/item') tempTable(item)
);
So I have one product that has many options(Which is the list of products passed in)
----
Now what I need to do is select all the options that belong to a product
--And the most important thing that I need, and is what I am after in this post is : I need to query and pull all options from the utbProductUtbProductOption table WHERE productID != to a @ProductID.
And I can not get this going yet.
I know this sounds easy but there is something somewhere that is not allowing me to get this to work. I am always ending up with no results.
Please ask any questions and I'll answer soon as possible.
Erik
Dam again!
-
Hey GrassHopper I finally got it.
I had to think backwards.
ALTER proc [dbo].[uspUtbProduct_Select_W_Top1Photo_4Opts]
@SubCategoryID INT,
@ProductID INT
AS
DECLARE @OptionsTable TABLE
(
ProductID INT,
SubCategoryID INT,
ProductName VARCHAR(30),
ProductNumber VARCHAR(30),
PhotoID INT,
PhotoName VARCHAR(30)
)
INSERT INTO @OptionsTable
SELECT pr.ProductID,
------------------------------
-- prPo.ProductOptionID,
------------------------------
pr.SubCategoryID,
pr.Name AS 'ProductName',
pr.Number AS 'ProductNumber'
------------------------------
,ph.[PhotoID]
,ph.[Name] AS 'PhotoName'
FROM Production.utbProduct pr WITH(NOLOCK)
left outer join (select ProductId, max(PhotoId) as PhotoId
from Production.utbProductUtbPhoto WITH(NOLOCK)
group by ProductId) as prph
on pr.ProductId = prph.ProductId
left outer join Files.utbPhoto ph
on prph.PhotoId = ph.PhotoId
left join Production.utbProductUtbProductOption prpo
on prpo.ProductID = pr.ProductID
WHERE pr.SubCategoryID = @SubCategoryID
AND pr.ProductID <> @ProductID
AND pr.ProductID NOT IN (SELECT ProductOptionID FROM Production.utbProductUtbProductOption
WHERE ProductID = @ProductID)
SELECT * FROM @OptionsTable t1
exec [dbo].[uspUtbProduct_Select_W_Top1Photo_4Opts]2,7
Dam again!
-
I'm glad you were able to figure it out. I was having trouble understanding why you needed a not equal relationship, as I would have thought that any given product option would have to have some direct relationship to the product it's an option for. My basis for thinking this was seeing both a product table and a product option table. I would have imagined they would be linked by a ProductID, directly, and unfortunately, while you now have your solution, I'm as confused as I was to begin with. Perhaps that's why you are an "old hand", and I'm still a "grasshopper"...
Here's where my brain went concept-wise in T-SQL:
DECLARE @ProductID intSET @ProductID = 12345
DECLARE @PRODUCT_TBL TABLE (
ProductID int,
ProductName varchar(25),
Qty_In_Stock bigint,
Product_Cost money(18,2),
Product_Retail money(18,2)
PRIMARY KEY (ProductID)
)
DECLARE @PRODUCT_OPTIONS TABLE (
ProductOptionID int,
ProductID int,
Option_Description varchar(40),
Option_Cost money(18,2),
Option_Retail money(18,2)
PRIMARY KEY (ProductOptionID)
)
SELECT *
FROM @PRODUCT_TBL AS A LEFT OUTER JOIN @PROUDCT_OPTIONS AS B
ON A.ProductID=B.ProductID
WHERE A.ProductID = @ProductID
Clearly, without any INSERTs, this code is concept only. Let me know where my concept went wrong so I can learn from the master. Thanks!
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I see what you're doing.
You are thinking in terms of if a product has options it needs to have a options table. And that is right as well.
Here is what I was thinking. I spent all this time defining a products table ( Even though I love to use the AdventureWorks as a temp, you would have to see the rest of my schema to realize that this is not a copy and paste ) and each one of my products that go into the products table can have options. So, I ask myself, "If a product is a product no mater if it is even an option of another product, the GD thing is still a product and options must be defined with the same style entity, because option is a product"
So why have two table the exact same? This way I can have one table and just define each product at will, and when I notice that the current product has options, "Like a light kit on a fan" then I just do a insert into my tween table.
---You can almost look at this as hierarchical, You know? Like the big cup on top of the smaller cup, and a bigger cup on top of that cup.
***What you are doing is write, however, you are doing twice the work and you have a bigger table to maintain.
Now ask yourself, "What if my product's option has product options? My design is complete, your design needs another table.
And I know this wouldn't work everywhere.
Erik
Dam again!
-
Now I see where you're at. It wasn't obvious to me from looking at your T-SQL that the potential for a heirarchical relationship beyond option and product beyond one level was involved. Perhaps a recursive CTE is in order then? I read about that recently and the concept was tantalizing, although I can't say I fully understood exactly how it achieves it's goal. Similarly to your product with an option that can be a product in it's own right, imagine the heirarchy of employees and their reporting relationships (Joe reports to Henry, who reports to Jeff, etc...). Assuming that all the employees are in one table, such as is the case with all your products and options, I would expect the following example might be applicable (it uses Northwind's employee table):
--Start with a covering index that will allow fetching direct subordinates-- of each manager using a single seek plus a partial scan
CREATE UNIQUE INDEX idx_mgr_emp_ifname_ilname
ON dbo.Employees(ReportsTo, EmployeeID)
INCLUDE(FirstName, LastName);
--Here's the recursive CTE, which identifies a specific employee whose
-- entire organization is the result set.
DECLARE @Employee int
SET @Employee = 5;
With EmpsCTE AS (
SELECT EmployeeID, ReportsTo, FirstName, LastName
FROM dbo.Employees
WHERE EmployeeID = @Employee
UNION ALL
SELECT EMP.EmployeeID, EMP.ReportsTo, EMP.FirstName,
EMP.LastName
FROM EmpsCTE AS MGR
JOIN dbo.Employees AS EMP
ON EMP.ReportsTo = MGR.EmployeeID
)
SELECT * FROM EmpsCTE;
The thing you have to watch out for is infinite recursion, which could occur if you ever had a product that could have itself as an option. However, you can specify a maximum recursion level as an option in the outer query to prevent recursion beyond some number of levels, which is 100 by default. Removing that limit entirely requires setting it to zero.
If I understand it correctly, the reference to the CTE within itself just means "the previous result set", and an empty result set stops the recursion at that level.
As useful as this method is, employees must of necessity ultimately report up to a single entity, whereas with products and options, each product is independent. Perhaps that would just mean that to apply this to your case, your anchor query (the one before UNION ALL) would have to be ALL your products? Of course, if not every record in your table represents a product and there are some records that only represent options, there would need to be a field in the record that could indicate that, and the anchor query would need to exclude those records.
Does that make sense?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
A product can never be an option for itself.
Dam again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply