Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can I handle the following query ?? Expand / Collapse
Author
Message
Posted Tuesday, October 29, 2013 10:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 7:16 AM
Points: 15, Visits: 56
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
Post #1509472
Posted Tuesday, October 29, 2013 11:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 3,919, Visits: 8,898
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(
ID int,
Name varchar(20),
ParentID int)
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(
ID int,
Name char(5),
CategoryID int)
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1509500
Posted Wednesday, October 30, 2013 5:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 7:16 AM
Points: 15, Visits: 56
Thanks a lot Luis .. Can I handle it without functions ?? is there any other alternative way ??
Post #1509727
Posted Wednesday, October 30, 2013 8:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 35,584, Visits: 32,174
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1509823
Posted Wednesday, October 30, 2013 9:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 3,919, Visits: 8,898
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1509833
Posted Wednesday, October 30, 2013 12:41 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:00 PM
Points: 4,477, Visits: 3,929
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
Post #1509909
Posted Wednesday, October 30, 2013 1:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 3,919, Visits: 8,898
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.

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



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1509915
Posted Thursday, October 31, 2013 5:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 7:16 AM
Points: 15, Visits: 56
Its Great .. I will use Function Solution Luis ..Thanks a lot for your help :)
Post #1510120
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse