Query advice - Multiple LEFT JOINS to same table or not??
Posted by:
Joby Tyler
Date: March 24, 2005 06:39AM
Hi - some noob help required.....wonder if anyone could give me some advice on the best way to achieve the following:
I have a table holding details of Pigeons (yes - bit random I know!), and each record has a field for each of its parents which corresponds to the pigeon_ID column in the same table.
When displaying the pigeon details, I need to get the data for each parent from the table, based on each parents Pigeon_ID_Number, and display the name and ring number, and a link to display that particular pigeons details.
To further complicate matters, for each of the parents, I need to go get their parents details aswell.
I currently have this working for one generation, using LEFT JOINS to the same table in the MySQL query.
Now I need to do the second iteration, I feel that there must be a neater way to do it - the query is going to be returning loads of columns at once.
My question is, should I be using all these LEFT JOINS, or should i be doing simple single record queries within my PHP display loop (i.e. fatherID=39 - go get record, mother ID=84 - go get record, fathers fatherID=63 go get record - etc.....)
Or is there another way that I dont know about yet - not done much of this and sort of learning as I go along........
Any assistance appreciated..........