Join 2 Tables

[Ell20]

Hey,

 

I have been going over my database/website and redoing the code so that I can reduce data redundancy etc.

 

I have done quiet alot but I am stuck on this particular one:

 

Users Table:

user_id

first_name

last_name

 

Teams Table:

captain

vice_captain

 

I have made it so that captain and vice_captain contain the user_id of the user that I want to display however I cant get it working:

 

$result = mysql_query("SELECT * FROM teams INNER JOIN users ON teams.captain = users.user_id AND teams.vice_captain = users.user_id WHERE teams.club_id = '$id' ORDER BY 'team_id' ASC LIMIT $from, $max_results") or die(mysql_error());

 

If I take away the vice_captain join bit I can get it to work but I cant get them to display the correct values together.

 

Appreciate any help

[lemmin]

Could you be more specific about what results you are trying to get from your query?

[Ell20]

I want to display the first_name and last_name of the captain and vice_captain by using the user_id rather than having to duplicate the name again.

 

Thanks

[paul2463]

for a kick off, if you use variables in a mysql_query statement they have to be wrapped in ''

 

so

$result = mysql_query("SELECT * FROM teams INNER JOIN users ON teams.captain = users.user_id AND teams.vice_captain = users.user_id WHERE teams.club_id = '$id' ORDER BY `team_id` ASC LIMIT '$from', '$max_results'") or die(mysql_error());

[Ell20]

Thanks for the reply, this was what I tried myself but it doesnt appear to work as it gives out no rows but it should give out 2 according to the database.

 

Cheers

[lemmin]

If you only have one captain and vice_captain per club_id, this should work, assuming I am right about what you are trying to do.

"SELECT first_name, last_name FROM Users WHERE user_id = (SELECT captain FROM Teams WHERE Teams.club_id = '$id') OR user_id = (SELECT vice_captain FROM Teams WHERE Teams.club_id = '$id')"

[Ell20]

Ill try and explain a bit clearer.

 

A club has lots of teams, each team has a captain and a vice_captain.

When you create a team the captain and vice_captain fields contain the user_id from the users table.

When I display the teams I want to display the captain and vice_captain however I dont want to display the user_id number I want to display the first_name and last_name of the user that is linked to that user_id.

 

Appreciate your help

[lemmin]

Yeah, that is what I wrote the query to do, did you try it?

[Ell20]

Error given out:

 

Subquery returns more than 1 row

[lemmin]

This code should work exactly how it is. Try it and tell me what happens.

$result= mysql_query("SELECT first_name, last_name FROM users WHERE user_id = (SELECT captain FROM teams WHERE club_id = '$id') OR user_id = (SELECT vice_captain FROM Teams WHERE Teams.club_id = '$id')") or die(mysql_error());

[Ell20]

Error given out:

 

Subquery returns more than 1 row

[lemmin]

That means that there is a club_id that has more than one captain or vice_captain asociated with it. The Where clause needs to decide which captain and vice that it is looking for. This means that your original way of doing it wouldn't have worked either. Take this part of the code and make it its own query to test with.

(SELECT captain FROM teams WHERE club_id = '$id')

 

make:

$result = mysql_query("SELECT captain FROM teams WHERE club_id = '$id'") or die(mysql_error());

 

and change the WHERE criteria until you can get this result to only contain 1 captain (user_id). If it is already containing one, change captain to vice_captain because it is one of those two.

 

Good luck!

[sasa]

try

SELECT c.first_name cap_first, c.last_name cap_last, v.first_name v_cap_firs, v.last_name v_cap_last FROM Teams 
LEFT JOIN users c ON captain=c.user_id 
LEFT JOIN users v ON vice_captain =v.user_id
WHERE Teams.club_id = '$id'

[Ell20]

Excellent that seems to have worked!!

 

Appreciate your help so much!!