Mysql query dependant on another query vs. join

Question

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['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?

entropo · Accepted Answer · 2011-04-18 19:23:49Z

1

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.

edited Apr 18, 2011 at 19:23

answered Apr 18, 2011 at 17:46

entropo

2,48115 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rico Over a year ago

I'd seen that and find it very helpful. Where I get lost is I how to I make sure only those values that match the specific team id are returned. i.e.: i only want the player name where the team ids are both = to a specific value. So if the team_id is = to 7 then I only want the players in table 2 that are have the team_id of 7.

Rico Over a year ago

GOT IT!!! Guess thinking for a second helps lol Here's my code:

$sql= "SELECT * FROM PLAYERS INNER JOIN TEAM_PLAYERS ON TEAM_PLAYERS.PLAYER_ID = PLAYERS.PLAYER_ID"; $result = mysql_query($sql) or die(mysql_error());  while($row = mysql_fetch_array($result)){ 	if ($row['TEAM_ID'] == $tid){ 		echo $row['PLAYER_NAME']; 	} }

entropo Over a year ago

That's one way to skin it, but to get this as pure SQL check out my edit.

Leslie Over a year ago

check out Understanding SQL Joins here: devshed.com/c/a/MySQL/Understanding-SQL-Joins

Rico Over a year ago

Thanks all! Will definitely check out the book store!

Collectives™ on Stack Overflow

Mysql query dependant on another query vs. join

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related