Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can I handle the following query ??


How can I handle the following query ??

Author
Message
ahmedhussein874
ahmedhussein874
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18080
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
   Wink
   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
ahmedhussein874
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 56
Thanks a lot Luis .. Can I handle it without functions ?? is there any other alternative way ??
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44766 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18080
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
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10188 Visits: 9535
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
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18080
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
ahmedhussein874
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 56
Its Great .. I will use Function Solution Luis ..Thanks a lot for your help Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search