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 order by ChildCount desc; Regards, Andy. 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]
