Yay! The real thing!
-
Finding all child / parent rows is easy. It's when you need to find all descendant / ancestor rows that you might need to turn to hierarchyid[^] for help. :) Tutorial: Using the hierarchyid Data Type[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
It's all ancestors and descendants I needed - and I managed it quite well: Never really got my head round recursive CTE's though! :laugh:
ALTER PROCEDURE [dbo].[spGetChildPages]
@PageID UNIQUEIDENTIFIER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Empty UNIQUEIDENTIFIER;
SET @Empty = dbo.fGetEmptyGuid();
WITH Children AS(
SELECT * FROM Pages WHERE ID = @PageID AND ID != @Empty
UNION ALL
SELECT p.* FROM Pages p
INNER JOIN Children c ON p.ParentID = c.ID
)
SELECT * FROM Children WHERE ID != @PageID;
ENDALTER PROCEDURE [dbo].[spGetParentPages]
@PageID UNIQUEIDENTIFIER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Empty UNIQUEIDENTIFIER;
SET @Empty = dbo.fGetEmptyGuid();
WITH Parents AS (
SELECT *, 1 AS Level FROM Pages WHERE ID = @PageID AND ParentID != @Empty
UNION ALL
SELECT p.*, Level+1 AS Level FROM Pages p
INNER JOIN Parents pp ON p.Id = pp.ParentId AND pp.ID != @Empty
)
SELECT ID, PageName, ParentID, SetID
FROM Parents
WHERE ID != @Empty AND ID != @PageID
ORDER BY level DESC
ENDI needed them for a hierarchical set of pages with a delete and restore feature.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
OriginalGriff wrote:
which tends to look like a stroke victim
And that explains why it is a hospital center... (according Wikipedia at least) :-D
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
Herself is cooking a Full English Breakfast for Brunch! It's gotta be over a year since I last had one (she's on a diet, which means I am too) and I've missed 'em! Stuff the Granola - I'm having the real thing today. (And after a morning spent working out SQL SP's to "find all Child" and "find all Parent" rows I damn well deserve it!)
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
OriginalGriff wrote:
a morning spent working out SQL SP's to "find all Child" and "find all Parent" rows
That's got to be at least a zillion calories eh?
New version: WinHeist Version 2.2.2 Beta
I told my psychiatrist that I was hearing voices in my head. He said you don't have a psychiatrist! -
OriginalGriff wrote:
a morning spent working out SQL SP's to "find all Child" and "find all Parent" rows
That's got to be at least a zillion calories eh?
New version: WinHeist Version 2.2.2 Beta
I told my psychiatrist that I was hearing voices in my head. He said you don't have a psychiatrist!Quite a few brain cells committed suicide, anyway. SQL syntax isn't the most obvious...
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt -
Quite a few brain cells committed suicide, anyway. SQL syntax isn't the most obvious...
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
Friends don't let friends do SQL! :) SQL leads to CRS
New version: WinHeist Version 2.2.2 Beta
I told my psychiatrist that I was hearing voices in my head. He said you don't have a psychiatrist! -
Friends don't let friends do SQL! :) SQL leads to CRS
New version: WinHeist Version 2.2.2 Beta
I told my psychiatrist that I was hearing voices in my head. He said you don't have a psychiatrist!Sometimes, you gotta do it in the server. :sigh:
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
It's all ancestors and descendants I needed - and I managed it quite well: Never really got my head round recursive CTE's though! :laugh:
ALTER PROCEDURE [dbo].[spGetChildPages]
@PageID UNIQUEIDENTIFIER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Empty UNIQUEIDENTIFIER;
SET @Empty = dbo.fGetEmptyGuid();
WITH Children AS(
SELECT * FROM Pages WHERE ID = @PageID AND ID != @Empty
UNION ALL
SELECT p.* FROM Pages p
INNER JOIN Children c ON p.ParentID = c.ID
)
SELECT * FROM Children WHERE ID != @PageID;
ENDALTER PROCEDURE [dbo].[spGetParentPages]
@PageID UNIQUEIDENTIFIER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Empty UNIQUEIDENTIFIER;
SET @Empty = dbo.fGetEmptyGuid();
WITH Parents AS (
SELECT *, 1 AS Level FROM Pages WHERE ID = @PageID AND ParentID != @Empty
UNION ALL
SELECT p.*, Level+1 AS Level FROM Pages p
INNER JOIN Parents pp ON p.Id = pp.ParentId AND pp.ID != @Empty
)
SELECT ID, PageName, ParentID, SetID
FROM Parents
WHERE ID != @Empty AND ID != @PageID
ORDER BY level DESC
ENDI needed them for a hierarchical set of pages with a delete and restore feature.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
Looks like you managed quite fine. Wait until you need to find the common ancestors of several nodes ;P (I've already done it[^]) One thing I'm interested in, why are you using an EmptyGuid instead of just using NULL? Oh, and BTW, I'm with Jeff Moden on HierarchyID, I don't use them.
Wrong is evil and must be defeated. - Jeff Ello
-
Looks like you managed quite fine. Wait until you need to find the common ancestors of several nodes ;P (I've already done it[^]) One thing I'm interested in, why are you using an EmptyGuid instead of just using NULL? Oh, and BTW, I'm with Jeff Moden on HierarchyID, I don't use them.
Wrong is evil and must be defeated. - Jeff Ello
It makes another part of the software easier, but I don't like it. I'm mulling it over and may change it back to null - not a problem yet, I'm doing the DL design first this time.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
-
It's never a pretty looking meal - it'd be thrown out of MasterChef on day one - but it's all about the flavours :cool:
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
MasterChef is a hoax, real people eat real food. MasterChef's recipes are too complex, only imaginary people can eat them.
* CALL APOGEE, SAY AARDWOLF * GCS d--- s-/++ a- C++++ U+++ P- L- E-- W++ N++ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t++ 5? X R++ tv-- b+ DI+++ D++ G e++>+++ h--- ++>+++ y+++* Weapons extension: ma- k++ F+2 X * Never pay more than 20 bucks for a computer game. * I'm a puny punmaker.
-
Looks like you managed quite fine. Wait until you need to find the common ancestors of several nodes ;P (I've already done it[^]) One thing I'm interested in, why are you using an EmptyGuid instead of just using NULL? Oh, and BTW, I'm with Jeff Moden on HierarchyID, I don't use them.
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
I don't use them
While I dislike the HID I love the concept and use it extensively. However I mine is a NodeKey and is a varchar field.
Never underestimate the power of human stupidity RAH
-
It's never a pretty looking meal - it'd be thrown out of MasterChef on day one - but it's all about the flavours :cool:
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
MasterChef is a hoax, real people eat real food. MasterChef's recipes are too complex, only imaginary people can eat them.
* CALL APOGEE, SAY AARDWOLF * GCS d--- s-/++ a- C++++ U+++ P- L- E-- W++ N++ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t++ 5? X R++ tv-- b+ DI+++ D++ G e++>+++ h--- ++>+++ y+++* Weapons extension: ma- k++ F+2 X * Never pay more than 20 bucks for a computer game. * I'm a puny punmaker.
That's one problem. Another is the size of the portion. Here are some jam droplets served on a cracker made of unicorn flour served on top of a small fillet of *protein of choice* together with a spoon full of *random rare ingredient* risotto. But most of these competitions are for top end restaurants where you leave with your wallet empty and stomach hungry after going through starter, main dish and dessert.
-
Reading the ingredients of a full English breakfast can one make hungry, but the images on the web are awful X| ... You may post one to correct the bug...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
Aah, but are you using an adjacency list or nested sets?
Follow my adventures with .NET Core at my new blog, Erisia Information Services.
-
MasterChef is a hoax, real people eat real food. MasterChef's recipes are too complex, only imaginary people can eat them.
* CALL APOGEE, SAY AARDWOLF * GCS d--- s-/++ a- C++++ U+++ P- L- E-- W++ N++ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t++ 5? X R++ tv-- b+ DI+++ D++ G e++>+++ h--- ++>+++ y+++* Weapons extension: ma- k++ F+2 X * Never pay more than 20 bucks for a computer game. * I'm a puny punmaker.
Oh, I'll eat nearly anything plated as well as on MasterChef, except tripe or tongue no matter how beautiful it looks.
Follow my adventures with .NET Core at my new blog, Erisia Information Services.
-
Have you ever tried bara lawr? X|
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
It looks quite nice.
Follow my adventures with .NET Core at my new blog, Erisia Information Services.
-
Oh, I'll eat nearly anything plated as well as on MasterChef, except tripe or tongue no matter how beautiful it looks.
Follow my adventures with .NET Core at my new blog, Erisia Information Services.
Both tripe and tongue are regional dishes in Italy - they are the poors' rich recipes. Cow tongue doesn't impress me much, while tripe depends entirely on how it's cooked, it can be really satisfying.
* CALL APOGEE, SAY AARDWOLF * GCS d--- s-/++ a- C++++ U+++ P- L- E-- W++ N++ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t++ 5? X R++ tv-- b+ DI+++ D++ G e++>+++ h--- ++>+++ y+++* Weapons extension: ma- k++ F+2 X * Never pay more than 20 bucks for a computer game. * I'm a puny punmaker.
-
It looks quite nice.
Follow my adventures with .NET Core at my new blog, Erisia Information Services.
So does Arsenic!
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
-
So does Arsenic!
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
Yes, but I've eaten seaweed before and enjoyed it.
Follow my adventures with .NET Core at my new blog, Erisia Information Services.
-
Jörgen Andersson wrote:
I don't use them
While I dislike the HID I love the concept and use it extensively. However I mine is a NodeKey and is a varchar field.
Never underestimate the power of human stupidity RAH
I don't have a problem with the concept, I have also used it myself, I just find the implementation a bit lacking. I prefer a combo of nested sets and adjacency lists instead of path enumeration.
Wrong is evil and must be defeated. - Jeff Ello
