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)

Your query and explanation does not make sense. What is `team_has_player`? Please explain what all the tables mean. — Gordon Linoff, Aug 18 '14 at 01:47
your table structure is not clear. what does league_has_player and team_has_player represent in the database - are these functions? — simo.3792, Aug 18 '14 at 01:49

score 1 · Accepted Answer · answered Aug 18 '14 at 01:51

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)

score 0 · Answer 2 · answered Aug 18 '14 at 01:58

Get all the players registered for the league, where they don't belong to a team in the league.

SELECT p.*, lp.league_id
FROM player p
JOIN league_has_player lp ON lp.player_id = p.id
WHERE NOT EXISTS (select 1 
                  FROM team_has_player tp
                  JOIN team t ON t.id = tp.team_id
                  AND tp.player_id = p.player_id
                  AND t.league_id = lp.league_id)

three tables relations SQL select

2 Answers2