Another join Q: fetch from 3 tables [solved, thx Vlad]
Posted: Wed Mar 18, 2009 7:53 am
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...
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
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'];
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