[SOLVED] How to query (sql) by listing the sports of the player doesn't belong to?

Issue

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;

Solution

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)

Answered By – nbk

Answer Checked By – Mildred Charles (BugsFixing Admin)

Leave a Reply

Your email address will not be published.