sports table
| id | sport name |
|---|---|
| 1 | basketball |
| 2 | volleyball |
| 3 | golf |
| 4 | baseball |
players table
| id | sport name |
|---|---|
| 1 | michael |
| 2 | stephen |
player sports table
| id | player_id (players.id) | sport.id (sports.id) |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 1 | 4 |
| 2 | 2 | 1 |
| 2 | 2 | 3 |
RESULT I WANT:
Michael (id: 1) should list the sports he doesn't belong to:
- (2) volleyball
- (3) golf
Stephen (id: 2) should list the sports he doesn't belong to:
- (2) volleyball
- (4) baseball
This was the sql i did for Michael, but it return all data from playersports table including Stephen sports
SELECT * FROM `sports` LEFT JOIN `playersports` ON `sports`.id = `playersports`.sport_id WHERE `playersports`.`player_id` != 1;
CodePudding user response：
You can exclude all sort_ids which belog to player 1
SELECT s.id,s.name
FROM sports s WHERE s.id NOT IN ( SELECT sport_id FROM player_sports WHERE player_id = 1)