[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

GrandParent Hierarchy MS SQL

Posted on 2012-08-29
4
Medium Priority
?
666 Views
Last Modified: 2012-09-14
Hello,

I've got a MS SQL query below, that lists results based upon ID, ParentID and (Should) based on GrandParent.... The first two parts work fine, but there's something not quite right with the GrandParent section --

SELECT 
P.ID, 
Title, 
PageType, 
CASE WHEN P.ParentID is NULL Then 0 ELSE P.ParentID END as ParentID, 
Live, 
Hidden, 
ISNull(Entries,0) Entries,
ISNull(GrandParent,0)GrandParent 
FROM MyPage P 
left join(select P2.ParentID, COUNT(P2.ID) Entries from dbo.MyPage P2 Group By P2.ParentID) P2 on P2.ParentID = P.ID 
left join(select P3.ID, COUNT(P3.ParentID) GrandParent from dbo.MyPage P3  Group By P3.ID) P3 on P3.ID = P.ParentID 
Where COALESCE(P.ParentID, 0) NOT IN (Select ID from dbo.MyPage where PageType = 3) 
ORDER BY CASE WHEN COALESCE(GrandParent,0) = 0 THEN P.ParentID ELSE GrandParent END, CASE WHEN COALESCE(P.ParentID, 0) = 0 THEN P.ID ELSE P.ParentID END, ID

Open in new window


Specifically, (I think) this line --

left join(select P3.ID, COUNT(P3.ParentID) GrandParent from dbo.MyPage P3  Group By P3.ID) P3 on P3.ID = P.ParentID 

Open in new window


For clarification there is no GrandParent Column...

I'm getting a 1 for GrandParent for 3 particular rows, but what i actually am looking for is --

ID = 315, ParentID = 313, GrandParent = 2

Any suggestions?

Thanks
0
Comment
Question by:garethtnash
  • 2
4 Comments
 

Author Comment

by:garethtnash
ID: 38344612
I'm guessing that I somehow need to change that line for something like --

Select COALESCE(ParentID,0) GrandParent from dbo.MyPage Where ID in(Select ParentID from dbo.MyPage) Group By ParentID

Open in new window


Would that be almost correct?

Thanks
0
 
LVL 12

Accepted Solution

by:
sachitjain earned 1000 total points
ID: 38344839
Query looks correct but I think you are confusing in understanding the result of this query

Just run this query
select P3.ID, COUNT(P3.ParentID) Parent from MyPage P3  Group By P3.ID

It would give you Id and count of parents for each Id then for each record in your actual query's output, match the values of above query's Id column and your actual query's ParentId column and then for each match, compare the value of your actual query's Grandparent column and above query's Parent column, I think then you would be able to understand the result correctly. The fact that is confusing is; that for each Id you are computing count of entries (kids) and count of grandparents so it is like for each level 3, you are computing count of level 4 and count of level 1.

To sample test your query, I created attached script that might help you
Grandparent.sql
0
 
LVL 12

Assisted Solution

by:Habib Pourfard
Habib Pourfard earned 1000 total points
ID: 38344867
try the following code:
SELECT  P.ID
       ,Title
       ,PageType
       ,CASE WHEN P.ParentID IS NULL THEN 0 ELSE P.ParentID END AS ParentID
       ,Live
       ,Hidden
       ,ISNULL(P2.Entries, 0) Entries
       ,ISNULL(P3.ParentID, 0) GrandParent
FROM    MyPage P 
LEFT JOIN ( SELECT ParentID ,COUNT(ID) Entries FROM MyPage GROUP BY ParentID ) P2 ON P2.ParentID = P.ID 
LEFT JOIN MyPage P3 ON P.ParentID = P3.ID
WHERE   COALESCE(P.ParentID, 0) NOT IN ( SELECT ID FROM MyPage WHERE  PageType = 3 )
ORDER BY CASE WHEN COALESCE(GrandParent, 0) = 0 THEN P.ParentID ELSE GrandParent END ,CASE WHEN COALESCE(P.ParentID, 0) = 0 THEN P.ID ELSE P.ParentID END ,ID

Open in new window

0
 

Author Closing Comment

by:garethtnash
ID: 38398878
Excellent Thank you
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question