Oh, well sense my table's rows aren't require in order for the other tables to work, maybe I don't need these extra rows.pytrin wrote:Those rows point to the actual rows in the linked table (a comments table for example). Hence, I can use one query to fetch all the information I need to render a subtree. For example, the subtree of comment with an id of 24 would be fetched with:Code: Select all
SELECT * FROM comments INNER JOIN comments_tree ON ancestor_id=24
One thing that I'm wondering though. How do I calculate the depth of a node when I'm selecting? I noticed in Bill's presentation that he mentions a depth column but never explained how to determine it. If I can figure out the depth of a node then I could achieve what I need in my OP.
[EDIT] I know that if I were to GROUP BY descendant_id and count descendant_id I would get the level/depth. And if I GROUP BY ancestor_id and count ancestor_id I would get the count. But how would I do both in the same query? Would I have to do a certain JOIN. Or is UNION my only option? I wonder why this doesn't work:
Code: Select all
SELECT COUNT(descendant_id) AS level
FROM TABLE
GROUP BY descendant_id
UNION
SELECT COUNT(ancestor_id) AS count
FROM TABLE
GROUP BY ancestor_id
[EDIT] I guess that's not how UNION works. UNION takes the results from each query and puts them into the same number of columns from each query. In other words, UNION appends the rows from the second query to the first.
I thought that UNION would be able to append another column to the first table. Is there a way to do this; with a JOIN maybe?
[EDIT] I know that the two queries I tried to UNION each return the level and count respectively. But I can't figure out how to group two columns without each of them effecting the other.