Ell20 Posted March 13, 2008 Share Posted March 13, 2008 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 Quote Link to comment Share on other sites More sharing options...
lemmin Posted March 13, 2008 Share Posted March 13, 2008 Could you be more specific about what results you are trying to get from your query? Quote Link to comment Share on other sites More sharing options...
Ell20 Posted March 13, 2008 Author Share Posted March 13, 2008 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 Quote Link to comment Share on other sites More sharing options...
paul2463 Posted March 13, 2008 Share Posted March 13, 2008 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()); Quote Link to comment Share on other sites More sharing options...
Ell20 Posted March 13, 2008 Author Share Posted March 13, 2008 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 Quote Link to comment Share on other sites More sharing options...
lemmin Posted March 13, 2008 Share Posted March 13, 2008 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')" Quote Link to comment Share on other sites More sharing options...
Ell20 Posted March 13, 2008 Author Share Posted March 13, 2008 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 Quote Link to comment Share on other sites More sharing options...
lemmin Posted March 13, 2008 Share Posted March 13, 2008 Yeah, that is what I wrote the query to do, did you try it? Quote Link to comment Share on other sites More sharing options...
Ell20 Posted March 13, 2008 Author Share Posted March 13, 2008 Error given out: Subquery returns more than 1 row Quote Link to comment Share on other sites More sharing options...
lemmin Posted March 13, 2008 Share Posted March 13, 2008 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()); Quote Link to comment Share on other sites More sharing options...
Ell20 Posted March 13, 2008 Author Share Posted March 13, 2008 Error given out: Subquery returns more than 1 row Quote Link to comment Share on other sites More sharing options...
lemmin Posted March 13, 2008 Share Posted March 13, 2008 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! Quote Link to comment Share on other sites More sharing options...
sasa Posted March 13, 2008 Share Posted March 13, 2008 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' Quote Link to comment Share on other sites More sharing options...
Ell20 Posted March 13, 2008 Author Share Posted March 13, 2008 Excellent that seems to have worked!! Appreciate your help so much!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.