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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

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

Post 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
Last edited by papa on Thu Mar 19, 2009 4:40 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Another join Q: fetch from 3 tables

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Another join Q: fetch from 3 tables

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Another join Q: fetch from 3 tables

Post by VladSun »

[sql]..., ug.firstName AS secondFighter_firstName, ...[/sql]
?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Another join Q: fetch from 3 tables

Post by papa »

That easy huh.. :)

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