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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

MS SQL - GrandParentID

Hello,

I have a table that allows for ParentID -

CREATE TABLE [dbo].[MyPage](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ParentID] [int] NULL,
	[PageType] [int] NULL,
	[Live] [char](1) NULL,
	[Hidden] [char](1) NULL,
	[DatePublished] [datetime] NULL,
	[EventStartDate] [datetime] NULL,
	[EventStartDateLabel] [nvarchar](100) NULL,
	[EventEndDate] [datetime] NULL,
	[EventEndDateLabel] [nvarchar](100) NULL,
	[UK] [char](1) NULL,
	[Eire] [char](1) NULL,
	[EU] [char](1) NULL,
	[ShareHolderOnly] [char](1) NULL,
	[Title] [nvarchar](100) NULL,
	[SEOLink] [nvarchar](100) NULL,
	[Summary] [nvarchar](350) NULL,
	[Content] [nvarchar](max) NULL,
	[ThumbImage] [nvarchar](150) NULL,
	[MainImage] [nvarchar](150) NULL,
	[Sticky] [char](1) NULL,
	[IncLightbox] [char](1) NULL,
	[Publisher] [int] NULL,
PRIMARY KEY CLUSTERED 
(

Open in new window


Currently I'm querying the table like so -

SELECT 
ID, 
Title, 
PageType, 
CASE WHEN ParentID is NULL Then 0 ELSE ParentID END as ParentID,
Live,
Hidden
FROM MyPage
Where ParentID NOT IN (Select ID from dbo.MyPage where PageType = 3)
ORDER BY CASE WHEN ParentID is NULL THEN ID ELSE ParentID END, ID 

Open in new window


See --

CASE WHEN ParentID is NULL Then 0 ELSE ParentID END as ParentID,

Now I want to add an additional section to this - GrandParentID, which would do the same as the line above, only for the record that relates to the line above....

Hope I'm not pushing my luck here, but if possible GreatGrandParentID & GreatGreat, & GreatGreatGreat...

but I'll settle with just GrandParentID if possible.

I would also like to be able to easily see if a row has child rows ('Entries')

Thank you
0
garethtnash
Asked:
garethtnash
3 Solutions
 
lwadwellCommented:
You could try a recurring cte.  Here is an example (untested):
;with recur as (
-- get the bottom most entry i.e. has no children
SELECT  id, title, pagetype, parentid, 0 as level
FROM MyPage
WHERE id NOT IN (SELECT parentid FROM MyPage)
-- get the parents, grandparents ... etc
UNION ALL
SELECT  mp.id, mp.title, mp.pagetype, mp.parentid, recur.level+1 as level
FROM MyPage mp JOIN recur ON recur.parentid = mp.id
)
SELECT * 
FROM recur

Open in new window

0
 
lwadwellCommented:
Or in the other direction:
;with recur as (
-- get the top most entry i.e. has no parents
SELECT  id, title, pagetype, parentid, 0 as level
FROM MyPage
WHERE parentid is null
-- get the children, grandchildren ... etc
UNION ALL
SELECT  mp.id, mp.title, mp.pagetype, mp.parentid, recur.level+1 as level
FROM MyPage mp JOIN recur ON mp.parentid = recur.id
)
SELECT * 
FROM recur

Open in new window

0
 
sachitjainCommented:
lwadwell suggestion is very valid. I further add into it


;with recur (Id, Title, PageType, ParentalHierarchy, [Level]) as (
-- get the top most entry i.e. has no parents
      SELECT  id, title, pagetype, cast('' as varchar(255)), 0 as level
      FROM MyPage
      WHERE parentid is null
      -- get the children, grandchildren ... etc
      UNION ALL
      SELECT  mp.id, mp.title, mp.pagetype, cast(recur.ParentalHierarchy + '->' + mp.parentid as varchar(255)), recur.level+1 as level
      FROM MyPage mp JOIN recur ON mp.parentid = recur.id
)
SELECT * FROM recur


With this query, you could actually see the complete parental hierarchy as one column value only.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
lwadwellCommented:
And another version extra columns for grandparentid, greatgrandparentid ... but unless you have the maximum depth ... it is limited.
;with recur as (
-- get the top most entry i.e. has no parents
SELECT  id, title, pagetype, 
        parentid, null gparentid, null ggparentid, null gggparentid, null ggggparentid,
        0 as level
FROM MyPage
WHERE parentid is null
-- get the children, grandchildren ... etc
UNION ALL
SELECT  mp.id, mp.title, mp.pagetype, 
        mp.parentid, recur.parentid, recur.gparentid, recur.ggparentid, recur.gggparentid,
        recur.level+1 as level
FROM MyPage mp JOIN recur ON mp.parentid = recur.id
)
SELECT * 
FROM recur

Open in new window

0
 
lwadwellCommented:
To do just the grandparent ... try:
SELECT 
a.ID, 
a.Title, 
a.PageType, 
CASE WHEN a.ParentID is NULL Then 0 ELSE a.ParentID END as ParentID,
CASE WHEN b.ParentID is NULL Then 0 ELSE b.ParentID END as GrandParentID,
a.Live,
a.Hidden
FROM MyPage a
LEFT JOIN MyPage b ON a.ParentId = b.ID
Where a.ParentID NOT IN (Select ID from dbo.MyPage where PageType = 3)
ORDER BY CASE WHEN a.ParentID is NULL THEN a.ID ELSE a.ParentID END, a.ID 

Open in new window

0
 
nishant joshiTechnology Development ConsultantCommented:
SELECT 
ID, 
Title, 
PageType, 
CASE WHEN ParentID is NULL Then 0 ELSE ParentID END as ParentID,
Live,
Hidden
FROM MyPage
Where ParentID NOT IN (Select ID from dbo.MyPage where PageType = 3)
ORDER BY CASE WHEN ID is NULL THEN GrandID WHEN ParentID is NULL THEN ID END, ID

Open in new window

0
 
garethtnashAuthor Commented:
Thank You
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now