Help with query - data from two tables

Think i may need to use join but never used it before

[mn1dbp]

I am a relative novice with a simple website using PHP and MySQL.

I need to create a query that involves data from two tables. I have given just the relevent tables and columns below.

Table 1 called horse_name contains columns horse_id (int), horse_name and sire (both varchar).

Table 2 called results contains columns horse_id and prize_money (both int fields).

I've done a query which lists all the horses and total prize money each has won with a sum query and group by horse_id but now I want to do a query that will list each sire and the total prize money won by all of their individual offspring. Not sure how i go about this.

[arran]

Hi mn1dbp,

If i understand your question correctly, the following query should do the trick:

select horse_name.sire, sum(results.prize_money) as 'total_winnings' from horse_name, results where horse_name.horse_id = results.horse_id group by horse_name.sire

arran.

[mn1dbp]

Works perfectly.

Seems I was making it more complicated than I needed to.

Many thanks for your help.

[mn1dbp]

Ok i am now trying to do a aquery which is again more complicated than the last.

Table 1 also has a column dam (varchar) which is the mother of the horse.

Now i don't want to do a list of dams and the total moeny won by their off spring as i did above for stallions. What i want is to do a list based on the stallion of the dam.

Each dam has their own row in the horse_name table (becuase they are a horse is their own right).

So i guess it would involved queries to first find the dam of the horse then find the sire of that dam and group money won by dam sire.

Is it best i list the dams in a seperate table or can it be done with the two tables i have?

[arran]

This isn't helped by the fact i know absolutely nothing about horses :)

If i've got the relationships correct, the following should work:

select stallion.horse_name, sum(results.prize_money) as 'total_money' from horse_name stallion, horse_name dam, horse_name offspring where stallion.horse_id = dam.sire and dam.horse_id = offspring.dam and offspring.horse_id = results.horse_id group by stallion.horse_name

arran.

[mn1dbp]

Many thanks for help Arran but in the end i just added a new column to table1 called dam_sire and used the same query as above.

I have one final thing. I wish to create a list based on the average prize money a runner gets for each stallion. For example if a stallion had total progeny earnings of 3000 (as calculated from the codein your msg #:2) and the number of runners to get this was 3 then the average would be 1000.

So far i have done this by using the first total query, alongside a count (distinct) query and then dividing one by the other. However i have done this a a PHP funtion after gathering the data so can't sort by it.

I tried using the avg function but this only brings back the average per run rather than per horse. e.g. in the above exampe if the 3 horses had run 20 times to get the 3000 then the avg funtion returns 150.

Is there anyway of doing it in a query?