May 28, 2004 at 8:18 am
Here's the situation - simplified and trivialized of course...
You have a table called Family that looks something like this:
fID Description ParentID
1 Mom NULL
2 Son 1
3 Daughter 1
4 Grandson 2
5 Grandson2 2
6 Grandson3 3
7 GGdaughter 4
Okay, you get the picture. It's just one table, and there's a hierarchy created by it that describes a family tree. No lets say I wanted to get all of the children of "Mom" returned in a query. It would look like:
SELECT * FROM Family WHERE ParentID =
(SELECT fID FROM Family WHERE [Description] = 'Mom')
That's easy. But now lets say you wanted to return EVERYONE that shares heritage with mom. In other words, her kids, her grandkids, her great grandkids, etc. The catch is, you don't know how many levels deep it goes - she might have only a single child, or she might have a family that goes on for 10 generations. SO the query has to be dynamic. I'm pretty sure I could do it with a cursor, but that's the cheap way out, and I'm betting it can be done with straight SQL a lot more efficiently. Thoughts?
Thanks,
Zac
May 28, 2004 at 8:48 am
You can handle this in a couple of different ways, depending on how frequently the data changes. The simple solution is to write a recursive query that walks the tree, either up or down depending on your needs. An alternative approach for a slowly changing dimension is to implement a "bridge" table. The concept for the bridge table is that you store the grandparent links in the same manner that the original links are stored. The base table exists exactly as your example, and is the surce of record for all insert/update/delete activity. The bridge table is used for rapid retrieval. By implementing a trigger on the base table, the bridge table is updated on any action. Populating the bridge table is typically handled by a set of recursive functions. Here is a sample bridge table, augmented with additional columns.
CREATE TABLE NodeTableBridge (
GID INT IDENTITY (1, 1) NOT NULL UNIQUE,
ParentID NUMERIC NOT NULL,
FOREIGN KEY REFERENCES A2B_MEMBER (MEMBER_ID),
ChildID NUMERIC NOT NULL, -- FOREIGN KEY REFERENCES A2B_MEMBER (MEMBER_ID),
IsTopParentInd VARCHAR(1) NOT NULL DEFAULT 'F' CHECK (IsTopParentInd IN ('T', 'F')),
IsBottomChildInd VARCHAR(1) NOT NULL DEFAULT 'F' CHECK (IsBottomChildInd IN ('T', 'F')),
ChildSequence INT NULL,
IsLastInSeqInd VARCHAR(1) NOT NULL DEFAULT 'F' CHECK (IsLastInSeqInd IN ('T', 'F')),
ChildDepthFromRoot INT NULL,
ChildDepthFromParent INT NULL,
ChildIsAssetInd VARCHAR(1) NOT NULL DEFAULT 'F' CHECK (ChildIsAssetInd IN ('T', 'F')),
AutoCalcChildInd VARCHAR(1) NOT NULL DEFAULT 'T' CHECK (AutoCalcChildInd IN ('T', 'F')),
BreadCrumbIDs varchar(7000) NULL,
BreadCrumbNames varchar(7000) NULL,
GetNodeTreeSeq numeric NULL--,
-- test and debug only ParentName varchar(50) NULL ,
-- test and debug only ChildName varchar(50) NULL
)
GO
CREATE CLUSTERED INDEX IDX_NodeTableBridge_PK ON NodeTableBridge (ParentID, ChildID)
GO
May 28, 2004 at 8:57 am
That's a great idea, except that adding tables is not an option. This needs to be a read only kind of query. You said that there's a way you could use a recursive query to walk the tree - but can it work even if you don't know how deep the tree goes? I think that's actually what I'm looking for. Can you show me what you mean?
Thanks,
Zac
May 28, 2004 at 9:24 am
Hi,
Check out the BOL on expanding hierarchies. There is a procedure for expanding trees.
Another way is to store information about the level in the hierarchy in the table and maintain this information by using triggers. see thread on Google
or search by "CONNECT BY" sqlserver on Google groups.
/Fredrik
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply