three tables relations SQL select

Question

I'm learning SQL, so help is greatly appreciated.

I have three tables: league, player and team

• league and player is many to many (as a player can be in more than one league)
• team and player is many to many (as a player can be on a team in multiple leagues)
• league and team is one to many.

I have the following table ids:

league.id  ----. league_has_player (league_id, player_id) .------   player.id
team.id    ----. team_has_player (team_id, player_id)     .-----  player.id
league     ----. team.id (team.league_id)

I want to get all the players in the league who are not on a team in the league.

Here's my broken attempt.

Select *
from player p,
    join team t on t.league_id = l.id 
    join league l on league.id = 2
where p.id = league_has_player.player_id and
     not in (select team_has_player.player_id from team_has_player)

Answer 1

I think you're trying more for something like this. As a couple comments have pointed out, your table schema isn't clear. But this is the impression I get from your attempt.

DECALRE @leagueId...

SELECT *
FROM player
WHERE player.id IN (SELECT player_id
                    FROM league_has_player
                    WHERE league_id = @leagueId) AND
      player.id NOT IN (SELECT player_id
                        FROM team_has_player
                            INNER JOIN team ON team.Id = team_has_player.team_id
                        WHERE team.league_id = @leagueId)