I have a problem that im currently trying to work through. I have table (called run_horses) that has columns as follows:
horse_id, sire_id, dam_id, damsire_id
then another table called sires that has:
both the sire_id and damsire_id correspond to the id from the sires table.
Now i want to display results from the run_horses table to display the sire_name from sires that corresponds to sire_id and damsire_id but as both get this from the sires table and it will be different names for both im not sure how i do this. I beleive it involved something called a self join? But have never used or have any experience of this.
As you guessed, you give the same table two different aliases: select sire.sire_name, damsire.sire_name from sires sire, sires damsire, run_horses where run_horses.sire_id = sire.id and run_horses.damsire_id = damsire.id
<added>Sorry storevalley, you beat me to it...</added>
Between posting and coming back to read i did a bit further research and came up with:
select t1.horse_name, t2.sire_name as 'sirename', t3.sire_name as 'damname' from run_horses AS t1, sires AS t2, sires AS t3 where (t1.sire=t2.id and t1.dam_sire=t3.id)
This seems to work fine. Now i need to incorporate it into a bigger query (having the smaller table names will also help here) but i find it helps to build up big queries from smaller ones that i have tested work.
Is there a name for what i have done - e.g. is it a type of join?