Mysql query dependant on another query vs. join
Stuck! So I've been researching how to run a query based on the result of another query and understand that it's not efficient (and I don't get the result I'd expect.)
Here's the info I have:
I have a $_Get variable for the team_id.
I have two tables. Table 1 and Table 2. Table 1 has team_id and player_id while Table 2 has all the player info.
I want to get the player_name in Table2. Look at what I had in mind below to get a better id:
//assign and clean up tid
$tid = mysql_real_escape_string($_GET[开发者_JS百科'tid']);
//query to find player id where the team id matches tid
$sql = "SELECT player_id FROM table1 WHERE team_id = '$tid'";
$result = mysql_query($sql);
//then I was basically going to run a while loop with another query to get the playerid
while ($row=mysql_fetch_array($result)){
$playerid=$row['player_id'];
$sql2 = "select player_name from table2 where player_id = '$playerid'";
$result2 = mysql_query($sql2);
echo $result2;
}
While researching this I've found that there seems to be a concensus that using mysql joins is the best way to achieve this without a while loop. I've played around with that option but don't really understand how joins work. How would I create a query that achieves the result I'd like?
Venn visualizations help a lot: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
Edit: I think this is what you're going for...
SELECT p.player_name
FROM players AS p
INNER JOIN team_players AS t ON (t.player_id = p.player_id)
WHERE t.team_id = $tid
I highly recommend picking up a SQL book. One that's written in very accessible language is Head First SQL.
精彩评论