Queries on The Result of a Query

Question

OK as a newcomer who is rapidly learning how to interogate a MySQL database such as Smartform I am rapidly hitting walls repeatedly trying to do more and more research on constructing queries and have come across the following problem which hopefully someone can help me with.On constructing a racecard for todays runners from the daily races and daily runners tables I have no probem seeing the days runners and races in which they are to compete along with the jockeys and trainers and form figures etc etc. However I would also like to pull out some information from the historical races and historical runners tables for each of the runners. eg ground it ran on official rating etc etc but cannot seem to work out how to do this unless I run a seperate query for each horse individually by naming it which is obviously very time consuming. What I would like to do is have a racecard with the extended from of each runner where it shows its previous runs and data related to those runs. In other words I would like an extended daily form card that shows each runners previous races in it underneath each horse name that shows historically run races by that horse. In simple terms it would be like having the information for each horse that you get when you look at a Racing Post racing card online and you see a horses runs by clicking on its name. This is obviously quite a complex type of query beyond my abilities at present but any help in being able to extract further information in this manner if at all possible without looking at individual horse names or trainers would hopefully prove very useful

Answer 1

How about creating a file with the days races and runners in and then using that file in the same way to link to the historic runners (using runner_id)  and in turn link to historic_races (joined using the race_id of historic_runners and historic_races) ?

 

This doesnt give you exactly what you want as Im not sure you can do that in SQL as all rows have the same format, But with the file below you could then write a program to show what in effect are header (current race details) and detail (historical race deatils) data

Assuming you have created a file called todays card which has a row per horse per race with some basic deatils

you can do an sql like this  which will give you a row per historic run per horse per todays race

Obviously you can add in more detail to the select part from hru and hra files

select td.*, hra.race_id as Hist_race_id  from todays_card td
join historic_runners hru using (runner_id)
join historic_races hra on (hru.race_id = hra.race_id)
order by td.scheduled_time, td.course, td.cloth_number ;

Comments

Why am I so dumb? :-(

An excellent idea and am disappointed that I didn't think of something similar. I shall go away and try this out and experiment with this approach until I get something close to what I want and then I am going to seek out an educational course so that I can learn a lot more about MySQL. Just seems too difficult for me to do it without professional tutoring or a structured learning course at a local college or via a correspondence course.

Many thanks Nick14 for your suggestions and advice