Search code examples
mysqlsqlinner-joinjoin

SQL - Can't figure out how to join three tables

I have four tables like this:

       **USERS**
___________________________
user_ID  username  password
---------------------------
1        user1     1234
2        user2     5678


              **TEAMS**
______________________________________  
team_ID  formation  team_name  user_ID
--------------------------------------
1        4-4-2      team1      1
2        4-3-3      team2      2

            **PLAYERS** 
____________________________________    
player_ID  name     position  rating
------------------------------------
1          Ronaldo  LW        94
2          Messi    RW        93
3          Hazard   LW        90

         **ACTIVE PLAYERS** 
___________________________________
ID  player_ID  team_ID  cardview_ID
-----------------------------------
1   1          2        9
2   3          1        7
3   2          1        3

Each user has a team with a formation and a team name. The "active players" tables references the player_ID with the team_ID to see which players are currently active on which teams.

Let's say that user1 logs in to the application, then I want to get all the players name, ratingand their cardview_ID. Something that should look like this:

_____________________________
name    rating    cardview_ID
-----------------------------
Hazard  90        7
Messi   94        3

These are the players that are currently active on user1's team which is team1.

How can I get this joined table? I have tried with an inner join but that didn't seem to do the work for me.

_______________________________ EDIT_____________________________________

This is the query that doesn't give the desired result:

SELECT players.name, players.rating, activeplayers.cardview_ID
FROM players
INNER JOIN
activeplayers
ON players.player_ID = usedplayers.player_ID

I also tried to join them on team_ID.

Solution

  • Assuming that the query in your post contains a typo and is actually this:

    SELECT players.name, players.rating, activeplayers.cardview_ID
FROM players
INNER JOIN
activeplayers
ON players.player_ID = activeplayers.player_ID

    This query will correctly return all the players who are active players. Now to limit it only to the team for User1, you need to add an additional join to the Teams table, same way you did the join above, and then add a WHERE clause that filters on the Teams.UserID.

    That's it.