John,
I was intrigued with the given solution so I tried this and here's a
version I found to work:
Table x1 has fields uid, uname, pid (parent's id)
select parent.uname, count( child.uname) as ChildCount
from x1 as parent left join x1 as child on ( parent.uID=child.pid)
group by parent.uName
Hi all,
Long time reader, first time poster, I hope that I get this right.
I am setting up a database that reflects a relationship between two records without
using the primary key as the tie between the two. I need to make a list of these
based on the number of children each has, and I am
suppose your table is people.
select people.Name, count( *) as cParent
from people left join people as p on ( people.ID=p.Parent)
group by people.Name order by cParent
I have not tested this.
You have to make indexes on Parent and ID.
Santino
At 8:36 -0500 15-08-2003, John Macon wrote:
Hi all,