CODEWITHSUNDEEP
mysql
Raphael_b
Raphael_b

Reputation: 1510

SQL JOIN 2 tables using the search result of one column

I have two tables I would like to join as followed:I have players who are part of teams. For one player I would like to check in which team he is playing and to display all details of the team). 

SELECT id_team 
    FROM team_by_membe
    WHERE id_membre = $id_membre (the team member I want to select)

SELECT name_team, id_organisation, etc
    FROM team
    WHERE id_team= (results of the above table)

My issue is that there are many results in the first SELECT. Any ideas?

Upvotes: 1

Views: 39

Answers (2)

Mureinik
Mureinik

Reputation: 311393

In order to avoid duplications caused by teams with multiple members, you could use the IN operator:

SELECT *
FROM   team
WHERE  id_team IN (SELECT id_team 
                   FROM   team_by_membe
                   WHERE  id_membre = $id_membre)

Upvotes: 1

vp_arth
vp_arth

Reputation: 14982

Something like this: 

"SELECT t.* FROM team t
INNER JOIN team_by_membe tbm ON tbm.id_team=t.id_team
WHERE tbm.id_membre = ?"

Upvotes: 0

Related Questions