Complicated Query...Not sure how else to Title it!

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

SQL Server Development (2000)

Complicated Query...Not sure how else to Title it!

Author

Topic

Jeepaholic
Starting Member

36 Posts

Posted - 2003-03-07 : 14:06:00

I have a self-referencing table used for navigation purposes. The database holds the "master" set, i.e. every navigation option possible. These navigation points are called Categories.

Within each Category can be a number of different Products, as well...each product can be within any number of different Categories. So, it is a many-to-many relationship.

Table Structure:


======================

CATEGORY
--------
ID
ParentID
Name


PRODUCT
-------
ID
Name
SelectID


CATEGORYPRODUCT
---------------
CategoryID
ProductID

=======================

.
.
My goal is the following: Using the PRODUCT.SelectID as an input, I would like to dynamically populate a navigation table that only includes the necessary CATEGORYs for each PRODUCT attached to the SelectID. The big caveot is that the navigation structure would also need to include any Parent trees required to GET to the appropriate CATEGORY. As well, it should be noted how many PRODUCTs exist in the CATEGORY, or if it is simply displayed for navigation purposes to get to a PRODUCT located in a subCATEGORY.

Below is some sample data:


==========================

CATEGORY
--------
ID	Parent	Name
1	0	Cat1
2	0	Cat2
3	1	Cat1.1
4	1	Cat1.2
5	4	Cat1.2.1
6	4	Cat1.2.2
7	6	Cat1.2.2.1
8	2	Cat2.1
9	2	Cat2.2


PRODUCT
-------
ID	Name	SelectID
1	Prod1	1
2	Prod2	1
3	Prod3	1
4	Prod4	1
5	Prod5	1
6	prod6	2
7	Prod7	2
8	Prod8	2
9	Prod9	2


CATEGORYPRODUCT
---------------
CategoryID	ProductID
1		1
3		2
7		3
1		4
9		5
6		6
6		7
7		8
7		9
1		7
1		2
1		3

=======================

Output should result in the following:

=======================

PRODUCT.SelectID = 1
CATEGORY.ID	CATEGORY.Parent		CATEGORY.Name		ProductsInCategory
1		0			Cat1			4
3		1			Cat1.1			1
7		6			Cat1.2.2.1		1
6		4			Cat1.2.2		0
4		1			Cat1.2			0
9		2			Cat2.2			1


PRODUCT.SelectID = 2
CATEGORY.ID	CATEGORY.Parent		CATEGORY.Name		ProductsInCategory
6		4			Cat1.2.2		2
4		1			Cat1.2			0
1		0			Cat1			1
7		6			Cat1.2.2.1		2

=======================

.
.
As you may see, only the trees with actual products somewhere in the sub-tree show up. As well, the trees are only displayed to the point where the deepest product exists within that tree.

So, now that *my* head is spinning...is yours? Any way to handle this? Please let me know if I can clarify any further.

Thanks for any thoughts!
Al

Edited by - Jeepaholic on 05/06/2003 21:21:35

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-07 : 15:08:23

Ok, I've got this far but now I need to understand how
these records pertain to selectionID 1??

6 4 Cat1.2.2 0
4 1 Cat1.2 0

DROP TABLE Category
CREATE TABLE Category
(
[ID] int,
[Parent] int,
[Name] nvarchar(25)
)

DROP TABLE Product
CREATE TABLE Product
(
[ID] int,
[Name] nvarchar(10),
[SelectID] int
)

DROP TABLE CategoryProduct
CREATE TABLE CategoryProduct
(
[CategoryID] int,
[ProductID] int
)

INSERT INTO Category([ID],Parent,[Name])
SELECT 1,0,'Cat1' UNION ALL
SELECT 2,0,'Cat2' UNION ALL
SELECT 3,1,'Cat1.1' UNION ALL
SELECT 4,1,'Cat1.2' UNION ALL
SELECT 5,4,'Cat1.2.1' UNION ALL
SELECT 6,4,'Cat1.2.2' UNION ALL
SELECT 7,6,'Cat1.2.2.1' UNION ALL
SELECT 8,2,'Cat2.1' UNION ALL
SELECT 9,2,'Cat2.2'

INSERT INTO Product([ID],[Name],SelectID)
SELECT 1,'Prod1',1 UNION ALL
SELECT 2,'Prod2',1 UNION ALL
SELECT 3,'Prod3',1 UNION ALL
SELECT 4,'Prod4',1 UNION ALL
SELECT 5,'Prod5',1 UNION ALL
SELECT 6,'Prod6',2 UNION ALL
SELECT 7,'Prod7',2 UNION ALL
SELECT 8,'Prod8',2 UNION ALL
SELECT 9,'Prod9',2

INSERT INTO CategoryProduct(CategoryID,ProductID)
SELECT 1,1 UNION ALL
SELECT 3,2 UNION ALL
SELECT 7,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 9,5 UNION ALL
SELECT 6,6 UNION ALL
SELECT 6,7 UNION ALL
SELECT 7,8 UNION ALL
SELECT 7,9 UNION ALL
SELECT 1,7 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3

DECLARE @SelectID int
SET @SelectID = 1

SELECT
Category.[ID],
Category.Parent,
Category.[Name],
(SELECT COUNT(ProductID) As ProductsInCategory
FROM Product A INNER JOIN CategoryProduct B ON A.[ID] = B.ProductID
WHERE Category.[ID]=B.CategoryID AND A.SelectID = @SelectID
) As ProductsInCategory
FROM Product
LEFT JOIN CategoryProduct on Product.[ID] = CategoryProduct.ProductID
LEFT JOIN Category on CategoryProduct.CategoryID = Category.[ID]
WHERE Product.SelectID = @SelectID
GROUP BY Category.[ID],Category.Parent,Category.[Name]

Edited by - ValterBorges on 03/07/2003 15:10:24

Jeepaholic
Starting Member

36 Posts

Posted - 2003-03-07 : 17:27:05

quote:

Ok, I've got this far but now I need to understand how
these records pertain to selectionID 1??

6 4 Cat1.2.2 0
4 1 Cat1.2 0

Hey there! I'll try to better explain what these records are for:

Even though there are no actual products directly under these two categories, they are required in the navigation structure to GET to the products that exist in sub-categories below them.

In this instance, there are products in:
Cat1.2.2.1
Cat1

There are NO products in:
Cat1.2
Cat1.2.2

However, in order to navigate hierarchically to Cat1.2.2.1, you need to go through Cat1.2 and Cat1.2.2 - which is why they need to be present in the results table. This way they can be displayed as navigation options.

Does this make sense?

Jeepaholic
Starting Member

36 Posts

Posted - 2003-03-13 : 21:31:27

No thoughts on this?

Jeepaholic
Starting Member

36 Posts

Posted - 2003-05-06 : 21:20:13

Bringing this one back to life... Still not sure how to go about pulling in the "missing" Categories that have no data in them...but are required since they are parents of the other navigation options. The query works fantastic otherwise!

Edited by - Jeepaholic on 05/06/2003 21:22:08

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-07 : 21:31:39

Not saying it's perfect, but I think this should return what you are looking for based on Valter's sample data:

select distinct c2.id, c2.name, c2.parent, sum(case when c2.name = a.name then no else 0 end) as ProductsInCategory
from
(
select c.id, c.name, c.parent, count(*) as no
from category c
inner join
categoryproduct cp
on
c.ID = cp.categoryID
inner join
product p on
cp.productID = p.id
where
p.selectID = @selectID
group by c.id, c.name, c.parent
) a
inner join
category c2 on
c2.name <= a.name
group by c2.id, c2.name, c2.parent

- Jeff

Jeepaholic
Starting Member

36 Posts

Posted - 2003-05-08 : 00:35:09

Hey Jeff,

Your example returns too much data, unfortunately. For @SelectID = 2 it returns Category's 1,4,6,7 (required) but also returns 3 and 5 (not required).

For @SelectID = 1 it returns ALL Category's 1-9, when only 1,3,4,6,7,9 are required.

As well, I noticed that you're using the names as a sort mechanism - I may not have been clear about this, but the ID is the primary key and the names are fictitious. In reality, they are real words (and may even repeat). The ID is important.

What seems to be required is a combination of what Valter's query returns (Categories with Products), and a recursive query that goes backwards from each Category w/Product and validates that ALL the parents are included.

Does this make sense? I'm not sure how to go about programming this...

Thoughts?

Edited by - Jeepaholic on 05/08/2003 00:37:15

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources