Page 1 of 1

Another join Q: fetch from 3 tables [solved, thx Vlad]

Posted: Wed Mar 18, 2009 7:53 am
by papa
Hi,

This time I need to fetch fighter name from fighter table, using fighterId1 and fighterId2. The problem is that I can only get one fighter per fight...

Code: Select all

 
$f_sql = "SELECT ue.eventId, ue.fighterId1, ue.fighterId2, ue.classId, ue.odds1, ue.odds2, ue.descr, ud.c_name, uf.firstName, uf.lastName
        FROM ufc_fight AS ue 
        JOIN ufc_class AS ud ON ue.classId = ud.classId
        JOIN ufc_fighter AS uf ON ue.fighterId1 = uf.fighterId
        WHERE ue.eventId = ".$row['eventId'];
 
If I add another join for fighterId2 I get error. Also it will probably write over my firstName, lastName for the first fighter. Any idea how I can display the two fighters at the same time or do I have to redesign everything?

the fight table:
eventId
fighterId1
fighterId2
etc

I want to get fighter name from fighter table using fighterId. Which works for one fighter, but don't know how to use fighterId2 and get firstName, lastName for that guy.

I want to display

event1
bob vs billy
pete vs stan

event2
brad vs sly
arnold vs barack

Re: Another join Q: fetch from 3 tables

Posted: Wed Mar 18, 2009 12:26 pm
by VladSun
papa wrote:If I add another join for fighterId2 I get error. Also it will probably write over my firstName, lastName for the first fighter. Any idea how I can display the two fighters at the same time or do I have to redesign everything?
Error message and you query, please ;)

Re: Another join Q: fetch from 3 tables

Posted: Thu Mar 19, 2009 3:17 am
by papa

Code: Select all

 
        //Fetch Fight 
        $f_sql = "SELECT ue.eventId, ue.fighterId1, ue.fighterId2, ue.classId, ue.odds1, ue.odds2, ue.descr, ud.c_name, uf.firstName, uf.lastName, uf.url, ug.firstName, ug.lastName, ug.url
        FROM ufc_fight AS ue 
        JOIN ufc_class AS ud ON ue.classId = ud.classId
        JOIN ufc_fighter AS uf ON ue.fighterId1 = uf.fighterId
        JOIN ufc_fighter AS ug ON ue.fighterId2 = ug.fighterId
        WHERE ue.eventId = ".$row['eventId'];
 
This one actually works, but exactly as I thought. It overwrites the uf.firstName with ug.firstName etc. Any way to fix this?

thanks

Re: Another join Q: fetch from 3 tables

Posted: Thu Mar 19, 2009 3:33 am
by VladSun
[sql]..., ug.firstName AS secondFighter_firstName, ...[/sql]
?

Re: Another join Q: fetch from 3 tables

Posted: Thu Mar 19, 2009 3:39 am
by papa
That easy huh.. :)

Thanks man! Thought I made a bad design descision but this works good. :mrgreen: