Need a little help on this one.

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)

Need a little help on this one.

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2003-03-15 : 16:51:47

I need some help with a Query that I cannot seem to come up with. It involves three tables.

Table [Products]
Which has two columns that are of concern here :
ProductID int
IsActive int (set to 0 inactive or 1 active)

Table [ProductCategory]
Which Contains
ProductID int
CategoryID int

Table [Categories]
Which Contains
UID int
ParentLevel int
ParentID int
Name nvarchar

What I am trying to do is to build a dynamic menu system based on these two tables.

In plain English here is what I am trying to Query"

ParentLevel in the Categories is either a zero (meaning it is a top category) or zero (meaning it is a subcategory) ParentID is present for a subcategory showing the top category that it belongs under.

CategoryID in the ProductCategory Table refers to the UID of the Category

I need to get all categories for products that have an active product (meaning that in the Products table the 'IsActive is set to 1), Sorted by the categories in a mneu fashion. First the top level group then all the categories that fall under this parent leve. It should be noted that the top levels will never have any products in them.

I have been working on this without a decent result yet, for about 3 hours and figured it was time to ask the experts. :)

Thanks in advance for any help.

Chris

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-03-16 : 17:16:21

Check out this article on trees and hierarchy:
http://www.sqlteam.com/item.asp?ItemID=8866

Sarah Berger MCSD

chrispy
Posting Yak Master

107 Posts

Posted - 2003-03-16 : 19:20:44

Thanks for the article tip. I was working with this earlier, and am afraid that I was still not able to get close.

Here is what I hace so far:

SELECT Categories.Name, Categories.uid, Categories.ParentLevel, Categories.ParentID
FROM Categories Categories_1 RIGHT OUTER JOIN
Categories ON Categories_1.uid = Categories.ParentID CROSS JOIN
Categories Categories_2
GROUP BY Categories.Name, Categories.uid, Categories.ParentLevel, Categories.ParentID, Categories_1.ParentID

Now this gives me all of the categories, both the parent and subparent just fine.

The hard part that I am having is getting the Joins to the ProductCategory table and the Products table (where I need to see if the product is active).

Any one have any thoughts?

Thanks in advance,
Chris

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-03-16 : 21:23:42

How about putting your query in a derived table, making another derived table of all categories with active products and doing a join?
SELECT C.* FROM(SELECT Categories.Name, Categories.uid, Categories.ParentLevel, Categories.ParentID
FROM Categories Categories_1 RIGHT OUTER JOIN
Categories ON Categories_1.uid = Categories.ParentID CROSS JOIN
Categories Categories_2
GROUP BY Categories.Name, Categories.uid, Categories.ParentLevel, Categories.ParentID, Categories_1.ParentID)C LEFT JOIN (SELECT PC.CategoryID FROM Products P INNER JOIN ProductCategory PC ON PC.ProductID = P.ProductID WHERE P.IsActive = 1)P ON P.CategoryID =
C.UID WHERE (P.CategoryID IS NOT NULL OR ParentLevel = 0)

I didn't acually test this, but it should work for single-level hierarchies. If you have multi-level hierarchies like Category-SubCategory-SubSubCategory it won't work because the SubCategory doesn't necessarily have items and its Parentlevel isn't 0. In that case you will need some more complex logic to assign a derived column for each row in Categories based on whether it is not the MAX(ParentLevel) in its hierarchy and therefore has no products, only subcategories, or you could try another approach instead of the one above to use a Case statement and test if a subcategory has any products at all.

Sarah Berger MCSD

chrispy
Posting Yak Master

107 Posts

Posted - 2003-03-16 : 23:29:35

Sarah,
WOW! Thanks for the help on this. Way beyond my level. I get the general idea of what this is doing. Of the bat it fails (invalid column name "productID").
I am going to play with it a little and see if I can get it to work.
Good news is there is only one sub, so I cam close.

Thanks again,
Chris

chrispy
Posting Yak Master

107 Posts

Posted - 2003-03-17 : 09:45:56

Almost there!

I figured out the problem with the product ID. It was my mistake. In the table Products there is no column ProductID as it is called uid.
So I got it running and it seems to be close. The top categories and the sub-categories are all displaying on the row.

Two problems I have now. The sub-categories seem to be displaying multiple times. I know there is a group by function that I am trying to place somewher, but with no luck.

Secondly a top-categories that has no products in the sub-category elements that isactive and are showing up.

I will try to find the group by clause that is needed, but the second problem is beyond me.

Here is waht I have so far.

SELECT C.*
FROM (SELECT Categories.Name, Categories.uid,Categories.ParentLevel, Categories.ParentID
FROM Categories Categories_1 RIGHT OUTER JOIN
Categories ON Categories_1.uid = Categories.ParentID CROSS JOIN
Categories Categories_2
GROUP BY Categories.Name, Categories.uid,Categories.ParentLevel,Categories.ParentID, Categories_1.ParentID) C LEFT OUTER JOIN
(SELECT PC.CategoryID
FROM Products P INNER JOIN
ProductCategory PC ON PC.ProductID = P.uid
WHERE P.IsActive = 1) P ON P.CategoryID = C.uid
WHERE (P.CategoryID IS NOT NULL) OR
(C.ParentLevel = 0)

Thanks again Sarah, for the help,
Chris

chrispy
Posting Yak Master

107 Posts

Posted - 2003-03-17 : 09:53:22

Ok I figured that out quicker than I thought (or should I say I got lucky).

I figured out the Group by clause. Now the difficult one. The top category that has no sub-categories with items in it.

Again , here is what I have:

SELECT C.*
FROM (SELECT Categories.Name, Categories.uid, Categories.ParentLevel, Categories.ParentID
FROM Categories Categories_1 RIGHT OUTER JOIN
Categories ON Categories_1.uid = Categories.ParentID CROSS JOIN
Categories Categories_2
GROUP BY Categories.Name, Categories.uid, Categories.ParentLevel, Categories.ParentID, Categories_1.ParentID) C LEFT OUTER JOIN
(SELECT PC.CategoryID
FROM Products P INNER JOIN
ProductCategory PC ON PC.ProductID = P.uid
WHERE (P.IsActive = 1)

chrispy
Posting Yak Master

107 Posts

Posted - 2003-03-17 : 10:02:29

Scratch that last post, as the statement posted incorrectly.

This is what I have.

SELECT C.*
FROM (SELECT Categories.Name, Categories.uid, Categories.ParentLevel, Categories.ParentID
FROM Categories Categories_1 RIGHT OUTER JOIN
Categories ON Categories_1.uid = Categories.ParentID CROSS JOIN
Categories Categories_2
GROUP BY Categories.Name, Categories.uid, Categories.ParentLevel, Categories.ParentID, Categories_1.ParentID) C LEFT OUTER JOIN
(SELECT PC.CategoryID
FROM Products P INNER JOIN
ProductCategory PC ON PC.ProductID = P.uid
WHERE P.IsActive = 1) P ON P.CategoryID = C.uid
WHERE (P.CategoryID IS NOT NULL) OR
(C.ParentLevel = 0)

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-03-17 : 14:01:18

Check this out:
SELECT C.*
FROM (SELECT Categories.Name, Categories.uid,Categories.ParentLevel, Categories.ParentID
FROM Categories Categories_1 RIGHT OUTER JOIN
Categories ON Categories_1.uid = Categories.ParentID CROSS JOIN
Categories Categories_2
GROUP BY Categories.Name, Categories.uid,Categories.ParentLevel,Categories.ParentID, Categories_1.ParentID) C LEFT OUTER JOIN
(SELECT PC.CategoryID
FROM Products P INNER JOIN
ProductCategory PC ON PC.ProductID = P.uid
WHERE P.Inactive = 0) P ON P.CategoryID = C.uid
WHERE P.CategoryID IS NOT NULL OR
(C.ParentLevel = 0 AND EXISTS(SELECT UID FROM Products P INNER JOIN ProductCategory PC ON P.UID =
PC.CategoryID INNER JOIN Categories ON PC.CategoryID = Categories.UId WHERE Categories.ParentID =
C.Uid AND P.Inactive = 0))

Again, I didn't check it. Also, make sure all the column names are OK.

Sarah Berger MCSD

chrispy
Posting Yak Master

107 Posts

Posted - 2003-03-19 : 12:51:44

I have been palying with this query for a couple of days and still no luck.

To be honest I am total lost on this one. The derived table is throwing me off.

I have tried to take this apart and try to get it to work, all to no avail.

I did change InActive to IsActive as it should be.

But all I can get out of the query is the error 'Ambiguous column name UID'.

I do know that it is coming from the Select UID clause, but using your example, I am unable to complete.

Any ideas?

Thanks again for all the help.

Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources