Need help on this query
-
Hello all. I'm almost getting my head to the wall :confused: because I can't get to the solution for a query I need to do. Is anyone here wanting to help me ? Let me explain the problem: I have a table named T1 with the following structure: ID | Subject | ParentID This table represents info in a form of tree and when I'm at the top branch the ID and ParentID fields have the same value. If I have the following data ID Subject ParentID 1 Sub1 1 2 Sub2 2 3 Sub1_1 1 4 Sub1_2 1 5 Sub1_1_1 3 I want to make a query with the following result: ID|Subject|NumberOfChildRecords e.g.: 1 | Sub1 | 2 2 | Sub2 | 0 3 | Sub1_1 | 1 4 | Sub1_2 | 0 5 | Sub1_1_1 | 0 Is this simple to do ? Thanks in advance for the attention you are having reading this.
-
Hello all. I'm almost getting my head to the wall :confused: because I can't get to the solution for a query I need to do. Is anyone here wanting to help me ? Let me explain the problem: I have a table named T1 with the following structure: ID | Subject | ParentID This table represents info in a form of tree and when I'm at the top branch the ID and ParentID fields have the same value. If I have the following data ID Subject ParentID 1 Sub1 1 2 Sub2 2 3 Sub1_1 1 4 Sub1_2 1 5 Sub1_1_1 3 I want to make a query with the following result: ID|Subject|NumberOfChildRecords e.g.: 1 | Sub1 | 2 2 | Sub2 | 0 3 | Sub1_1 | 1 4 | Sub1_2 | 0 5 | Sub1_1_1 | 0 Is this simple to do ? Thanks in advance for the attention you are having reading this.
I dont think it is simple... but you could certainly try something like: Here is how you would do it using NW: select a.employeeid, count(b.employeeid) from employees a, employees b where a.employeeid = b.reportsto group by a.employeeid What we have is a self referencing table. What we need to do is essentially add the table twice in the from clause with two seperate aliases and then correlate the two in the where. I should be using ANSI standard joins here, but it was quick. so for you we need something like select a.id, count(b.id) from T1 a, T1 b where a.id = b.parentid group by a.id; Let me know if this works. http://www.jasncab.com/huberblog :: Jason Huber