Page 2 of 2

Re: Multi-dimensional table

Posted: Tue May 05, 2009 7:10 pm
by JellyFish
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
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.

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
 
It seems to only be give the results to the first SELECT query.

[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.

Re: Multi-dimensional table

Posted: Wed May 06, 2009 2:37 am
by Eran
Because of the different grouping you can't efficiently do both in the same query. It's better to use two queries in this instance.

I was wondering if you were aware of what UNION does because of the previous query you showed which I thought was more suited for a join

Re: Multi-dimensional table

Posted: Wed May 06, 2009 11:41 am
by JellyFish
pytrin wrote:Because of the different grouping you can't efficiently do both in the same query. It's better to use two queries in this instance.

I was wondering if you were aware of what UNION does because of the previous query you showed which I thought was more suited for a join
It's true, I wasn't aware of what UNION does exactly, but eventually understood.

If it's better to separate the queries, how would I know which level has which count? I need two column in one table: level and count. If I were to make two separate queries, how would I know which row in one result set corresponds to another row in the other result set?

This is my table:

Code: Select all

 
+-------------+---------------+
| ancestor_id | descendant_id |
+-------------+---------------+
|      1      |       2       |
+-------------+---------------+
|      1      |       3       |
+-------------+---------------+
|      1      |       4       |
+-------------+---------------+
|      1      |       5       |
+-------------+---------------+
|      1      |       6       |
+-------------+---------------+
|      2      |       3       |
+-------------+---------------+
|      2      |       5       |
+-------------+---------------+
|      2      |       6       |
+-------------+---------------+
|      3      |       5       |
+-------------+---------------+
|      3      |       6       |
+-------------+---------------+
|      7      |       8       |
+-------------+---------------+
 
I notice that if I were to count the unique ancestor_id(s) I would have the count for each level. If I were to count the unique descendant_id(s) I would get the level. The only thing is, if I GROUP BY any one of these columns it would reduce the result set to were I couldn't to the second COUNT. I wonder if I can GROUP BY * and still use the COUNT functions. I'm going to go try that.

[EDIT] I tried GROUP BY * and got a syntax error. But I tried comma separating all columns and that didn't reduce the result set, but the COUNT functions didn't work. Also found that AND separating the columns in the GROUP BY clause reduced the entire table, which is expected.

[EDIT]I guess it's not a matter of using the COUNT function, because it counts the number of rows in a group. But rather, it's a matter of the GROUP BY. Maybe if I could combine two tables from two separate queries, it would solve the issue.

[EDIT] Bottom line: I need a way to take my table above and return a result set with two columns level and num_of_children. Level would be the number of levels down of a node from the top most parent and num_of_children would be the number of immediate children that node contains. How can I do this?