StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
7642788
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2011-10-04T02:44:42.830
FavoriteCount
0
LastActivityDate
2011-10-04T02:44:42.830
LastEditDate
LastEditorUserId
0
OwnerUserId
146077
ParentId
7462420
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
A self-referential representation of hierarchy is a bit clunky for jobs like - you want to select only one branch, so you'll need to recursively join onto the target table an unknown number of times. Very possible, but any time I work with hierarchies in SQL Server I jump straight to the <a href="http://technet.microsoft.com/en-us/library/bb677290.aspx" rel="nofollow">HierarchyId</a>. I don't know if we can recursively look both up and down the tree at the same time; a naive approach fails for me, so I'll present a simpler alternative. You already have the current node. Get the children of that node, and then get the parents of that node. Union them and you're done. And the easiest way to do recursive joins in SQL is with <a href="http://msdn.microsoft.com/en-us/library/ms186243.aspx" rel="nofollow">Common Table Expressions</a>. <pre><code>DECLARE @nodeid INT = 4 DECLARE @nodes TABLE (NodeID INT) ; WITH Parents (NodeID) AS ( -- get the parent of the current node SELECT FK_ParentNodeID FROM NodeHierarchy WHERE PK_NodeID = @nodeId -- not sure if 'null' is a valid parent, but I'm assuming not AND FK_ParentNodeID IS NOT NULL UNION ALL -- recursively get the parents of the parent SELECT FK_ParentNodeID FROM NodeHierarchy INNER JOIN Parents ON PK_NodeID = NodeID WHERE FK_ParentNodeID IS NOT NULL ) INSERT @nodes SELECT NodeID FROM Parents ; WITH Childs (NodeID) AS ( -- selecting the current node SELECT PK_NodeID FROM NodeHierarchy WHERE PK_NodeID = @nodeId UNION ALL -- recursively select the children of the branch SELECT PK_NodeID FROM NodeHierarchy INNER JOIN Childs ON FK_ParentNodeID = NodeID ) INSERT @nodes SELECT NodeID FROM Childs SELECT * FROM @nodes </code></pre> Now based on your previous question, you simply need to select from your existing views. <pre><code>SELECT Node, Label FROM NodeLabels WHERE Node IN (SELECT NodeID FROM @nodes) SELECT Parent, Child FROM Edges WHERE Parent IN (SELECT NodeID FROM @nodes) </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POHow to visualize only the descendants, ancestors, and self of a node in a hierarchy?
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USKirk Broadhurst
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.