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,
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 having a problem with the query set up. Currently I have the query set up to run at number of users +1, and that doesn't seem to be very efficient.
The records are something like this.
user ID number Name Parent
1 John 0 2 Don 1 3 Joe 1 4 Jack 2 5 Jill 4 6 Jane 4
okay, now I need to be able to make a list of the names, with the number of children that they have, then sort them in order of how many children they have. Basically, I want to make a list that has in numerical order the names of the parent, and how many children they have. I hope that it makes sense the way that I have described it. I have it working, but if my list of parents and children gets long, then it will kill me i think.
The above list would display as Name Number of Children John 2 Jack 2 Don 1 Joe 0 Jill 0 Jane 0
The way that I have it working right now, is I run a query to find out the number of records, then I run a query based on the first user (1), find all the children that belong to that user and store it in an array, then another query based on the second record to discover how many children that user has, so on and so forth until the end of the table is reached. Then i sort the array based on the number of children, the person with the most children goes at the top, and it descends from there. As you can see, I am running a query to find out the number of records, then a query based on each record. If my table gets to 50,000 users then that will be a nightmare I think.
Any help to point me in the right direction would be appreciated, I am using MySql 4.x, my local testing server is a Win2k machine, but my web server is a Linux box. I am mainly just looking for a concept for the better query, not the actual code to write it. I just can't seem to think of a better way to structure it.
Thanks!
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
