从我的连接表中获取数据

Question

I have a join table which takes the id from my respondents table respondant_id and the id from my teams table table_id.

The output is fine when I SELECT from that table so I get back the respondants ID married up with the teams ID.

I am wanting to show the respondents name from respondant_data and the team name from teams by using the values output from the join table.

I have attempted this here but I keep getting 0 results.

$sql = "
SELECT 
respondant_data.respondant_id, teams.team_id
FROM 
    respondant_data
INNER JOIN 
    teams 
ON
    respondant_data.respondant_id = teams.team_id
WHERE 
    respondant_teams.team_id= 5";

$result = $conn->query($sql);

$i = 1;

if($result->num_rows > 0){
    while($row = $result->fetch_assoc()){
        echo $i++ . ' ';
        echo 'user_id: ' . $row["respondant_id"] . ', ';
        echo 'team_id: ' . $row["team_id"];
        echo '<br>';        
    }
} else{
    echo 'no results';
}

So I want my output to be like 'John Smith', 'Central Team'

Answer 1

Try this query.

SELECT 
    resp_data.respondant_id, teams.team_id
FROM
    respondant_data resp_data,
    teams,
    respondant_teams resp_teams
WHERE
    resp_data.respondant_id = teams.team_id
        and resp_teams.team_id = teams.team_id
        and resp_teams.team_id = 5