How can I handle the following query ??

Question

How can I handle the following query ??

ahmedhussein874

SSC-Addicted

Points: 467
More actions
October 29, 2013 at 10:53 am

#300594

I have 2 tables "Category & Item" .. In the first table "Category" .. I have a self join Relation.
*The First Table Represent Main Categories which allow user to access items which are related to specific category
*The Second Table Represent The Items which is related to each category
*Each Category may be a child or parent for other categories
--I want to know how can I get the full items count for specific category and his child items hierarchy tree .. (not only the items which is related to that category only .. we shall get the count for the items which related to his child also = Indirect Relation)
Examples :
#The Items Count for "Electronic" Category will be 8
#The Items Count for "TV" Category will be 4
#The Items Count for "Cell Phones" Category will be 4
#The Items Count for "LCD" Category will be 1
#The Items Count for "LED" Category will be 1
#The Items Count for "Smart Phone" Category will be 1
#The Items Count for "3G Phone" Category will be 1
First Table (Category) :
ID , Name , ParentID
1 , Electronics , 0
2 , TV , 1
3 , Cell Phones , 1
4 , LCD , 2
5 , LED , 2
6 , Smart Phone , 3
7 , 3G Phone , 3
8 , Clothes , 0
Second Table (Item) :
ID , Name , CategoryID
1 , item1 , 2
2 , item2 , 4
3 , item3 , 5
4 , item4 , 7
5 , item5 , 6
6 , item6 , 3
7 , item7 , 8
8 , item8 , 3
9 , item9 , 2
So any Ideas for how can i get the above query result for these 2 tables ???
Notes :
-I can have many levels in the tree hierarchy
-Items can be linked to any category levl

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

There might be several ways to go, but here's an option using a recursive query on a table-valued function. Before implementing this solution, be sure to understand what's going on and feel free to ask anything.

DDL & Sample Data

CREATE TABLE Category(

IDint,

Namevarchar(20),

ParentIDint)

INSERT INTO Category VALUES

(1 , 'Electronics' , 0),

(2 , 'TV' , 1),

(3 , 'Cell Phones' , 1),

(4 , 'LCD' , 2),

(5 , 'LED' , 2),

(6 , 'Smart Phone' , 3),

(7 , '3G Phone' , 3),

(8 , 'Clothes' , 0)

GO

CREATE TABLE Item(

IDint,

Namechar(5),

CategoryIDint)

INSERT INTO Item VALUES

(1 , 'item1' , 2),

(2 , 'item2' , 4),

(3 , 'item3' , 5),

(4 , 'item4' , 7),

(5 , 'item5' , 6),

(6 , 'item6' , 3),

(7 , 'item7' , 8),

(8 , 'item8' , 3),

(9 , 'item9' , 2)

GO

Function declaration

CREATE FUNCTION Categories_List( @Category int)

RETURNS TABLE

AS

RETURN

(

WITH rCTE AS(

SELECT *

FROM Category

WHERE id = @Category

UNION ALL

SELECT c.*

FROM Category c

JOIN rCTE r ON c.ParentID = r.ID

)

SELECT ID

FROM rCTE

)

GO

Final query

SELECT c.ID, c.Name, COUNT(*)

FROM Category c

CROSS APPLY dbo.Categories_List( c.ID) l

JOIN Item i ON l.ID = i.CategoryID

GROUP BY c.ID, c.Name

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ahmedhussein874 SSC-Addicted Points: 467 More actions · Answer 2

Thanks a lot Luis .. Can I handle it without functions ?? is there any other alternative way ??

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

ahmedhussein874 (10/30/2013)
Thanks a lot Luis .. Can I handle it without functions ?? is there any other alternative way ??

Why do you want to do it without the use of high performance, easy to use, inline table valued functions like Luis used?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

ahmedhussein874 (10/30/2013)
Thanks a lot Luis .. Can I handle it without functions ?? is there any other alternative way ??

There might be, but it might take me a while to find something with good performance and I have work of my own. Is there any good reason to find a different way?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

Luis Cazares (10/30/2013)
ahmedhussein874 (10/30/2013)
Thanks a lot Luis .. Can I handle it without functions ?? is there any other alternative way ??
There might be, but it might take me a while to find something with good performance and I have work of my own. Is there any good reason to find a different way?

Luis, that's impressive. You essentially wrote the equivalent of Oracle's START WITH...CONNECT BY in SQL Server. As a bonus, you did it in a high-performance, compact and cool way. Seriously great work.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

Ed Wagner (10/30/2013)
Luis Cazares (10/30/2013)
ahmedhussein874 (10/30/2013)
Thanks a lot Luis .. Can I handle it without functions ?? is there any other alternative way ??
There might be, but it might take me a while to find something with good performance and I have work of my own. Is there any good reason to find a different way?
Luis, that's impressive. You essentially wrote the equivalent of Oracle's START WITH...CONNECT BY in SQL Server. As a bonus, you did it in a high-performance, compact and cool way. Seriously great work.

:blush: A good inspiration moment got me a great compliment. Thak you, Ed.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ahmedhussein874 SSC-Addicted Points: 467 More actions · Answer 7

Its Great .. I will use Function Solution Luis ..Thanks a lot for your help 🙂