开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜