------=_NextPart_000_04B4_01C36308.415CE100
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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. =20
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!
------=_NextPart_000_04B4_01C36308.415CE100--
