need help with 3 table join.

[cdoyle]

Hi,

I'm having a problem with a join of 3 tables, and displaying some fields.

 

I have created a new table called 'medical_ward'

This table consists of 4 fields (see below).

 

I've been able to make it so, when a player battles another player.  It inserts the correct data into my medical_ward table. 

But now I want to create a page, that displays this information.  So I need to create join between my new table and the other tables.

This is where I'm getting stuck.  I want to display the name of the player that was killed, and also the name of the player that won.

 

I started to try and create a query, but I know this isn't right.

and then I got stuck, when I thought about how to display the user name of both the killed player and the winner since the name will come from the 'username' field.

 

$getdead2 = $db->execute("SELECT medical_ward.Killed_By_ID, medical_ward.playerdead_ID, medical_ward.Weapon_Used_ID, medical_ward.Time_Left, players.id, players.username, blueprint_items.id, blueprint_items.name, from medical_ward LEFT JOIN players on medical_ward.playerdead_ID=players,ID LEFT JOIN blueprint_items on medical_ward.Weapon_Used_ID=blueprint_items.id");

 

medical_ward table

 

HOS_ID_PK

playerdead_ID.....(the username of the player that is dead)

Killed_By_ID........(the username of the player that killed the player) 

Weapon_Used_ID.(the weapon used)

 

players table

 

id

username

 

blueprint_items

id

name <<(the weapon name)

 

 

any ideas?

[Barand]

You join twice to the players table with different table aliases (p1 and p2) so although it's one physical table the query treats it as two logical tables

SELECT p1.username as deadplayer, p2.username as killedby, b.name as weapon
FROM medical_ward m
INNER JOIN players p1 ON m.playerdead_ID = p1.id
INNER JOIN players p2 ON m.Killed_By_ID = p2.id
INNER JOIN blueprint_items b ON m.Weapon_Used_ID = b.id

[cdoyle]

Thank You!

 

I knew I had to something with an Alias, but I couldn't remember how to do it!

I tried searching but just wasn't getting what I needed.

 

I just gave this a try, and it worked great!

 

[cdoyle]

I found a little glitch with my code.

 

If a users is defeated by someone with no weapons equipped, it gets entered into the medical_ward table as a 0 in the weapon_used field.

 

but since there is no ID '0' in my blueprints table, when the query is ran it shows no records.

 

Is there a way to make it so, it will still display the records even if there is no weapon used?

Maybe even have it default to 'hands' or something if there is a 0?

 

$getdead = $db->execute("SELECT p1.username as deadplayer, p2.username as killedby, b.name as weapon, Time_Left FROM medical_ward m 
	INNER JOIN players p1 ON m.playerdead_ID = p1.id 
	INNER JOIN players p2 ON m.Killed_By_ID = p2.id 
	INNER JOIN blueprint_items b ON m.Weapon_Used_ID = b.id
	ORDER BY Time_Left");

[cdoyle]

well I found a way around my problem, not sure if this is right.

 

I just created a new item in my blueprints table called fists.

 

Then when my insert query runs and inserts into the medical ward table, if no weapon is selected it uses the ID of the 'fists' row.

the query seems to work now.