Mysql Self Join to find a parent child relationship in the same table

Question

Im trying to calculate the amount of money won by all the offspring of a male race horse (Sire) over a time period. Listed by the Sire with the most amount of money won. I run the query and get the result Im after with one problem, I cant display the sires name, only their ID.

SELECT  `horses`.`SireID` AS  `SireID` , `horses`.`HorseName` AS  `Sire Name`, 
                  COUNT(  `runs`.`HorsesID` ) AS  `Runs` , 
                  COUNT( 
                           CASE WHEN  `runs`.`Finish` =1
                                THEN 1 
                                ELSE NULL 
                                END ) AS  `Wins` , 
                  CONCAT( FORMAT( (
                                COUNT( 
                                       CASE WHEN  `runs`.`Finish` =1
                                            THEN 1 
                                            ELSE NULL 
                                            END ) / COUNT
                                    (  `runs`.`TrainersID` ) ) *100, 0 ) ,  '%'
                  ) AS  `Percent` , 
           FORMAT( SUM(  `runs`.`StakeWon` ) , 0 ) AS  `Stakes` 
FROM runs
INNER JOIN horses ON runs.HorsesID = horses.HorsesID
INNER JOIN races ON runs.RacesID = races.RacesID
WHERE  `races`.`RaceDate` >= STR_TO_DATE(  '2012,07,01',  '%Y,%m,%d' ) 
AND  `races`.`RaceDate` < STR_TO_DATE(  '2012,07,01',  '%Y,%m,%d' ) + INTERVAL 1 
MONTH 
AND `horses`.`SireID`  <> `horses`.`HorsesID`
GROUP BY  `horses`.`SireID`, `horses`.`HorseName`
ORDER BY SUM(  `runs`.`StakeWon` ) DESC

Take a record in the horse table for example, a horse has a horsesID and they also have a sireID (their father). The sireID has an equivalent horsesID in another record in the same table as it is also a horse

Basically I need to map the horseName to the sireID.

I thought a self join would work.

 `AND `horses`.`SireID`  <> `horses`.`HorsesID``

but it doesn't return the correct Sire name corresponding to the SireID.

score 8 · Accepted Answer · answered Oct 02 '12 at 22:07

8

You can do a JOIN on the table itself. Here's a simpler example:

SELECT Horses.HorseID, Horses.HorseName, Horses.SireID, b.HorseName as SireName
FROM Horses
LEFT JOIN Horses b ON (Horses.SireID = b.HorseID)

You can probably figure out how to add the conditions from here.

answered Oct 02 '12 at 22:07

Stephen O'Flynn

2,309
23
34

Ok great that works :) I didn't think a LEFT JOiN could work for this, thanks for the enlightenment. – stemie Oct 03 '12 at 09:12

score 1 · Answer 2 · edited Jun 16 '20 at 21:04

1

join horses sires on sires.HorsesID = horses.SireID

edited Jun 16 '20 at 21:04

Marcello B.

4,177
11
45
65

answered Oct 02 '12 at 17:19

ilan berci

3,883
1
16
21

There is no sires table. Sires appears in the horse table in a parent child relationship. SireID, HorseID, Horsename. – stemie Oct 02 '12 at 17:51
I realize that there is no sires table.. we are rejoining to the horses table and then naming the association: "sires". So we now have 2 associations to the horses table.. one using the default 'horses'.. and the other 'sires' – ilan berci Oct 02 '12 at 18:03
Im confused as to how I can do that? I keep on getting "Unknown column" – stemie Oct 02 '12 at 20:15
Thanks for your help, I accepted Stephens answer above as it was easier for a noob like me to understand. – stemie Oct 03 '12 at 09:10

Mysql Self Join to find a parent child relationship in the same table

2 Answers2

Linked