Dan Dan - 13 days ago 5
MySQL Question

Getting data from my join table

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

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
Comments