How to use NOT IN in SQL?

Question

Game(p1, p2, pointsp1, pointsp2, ),
Participant(name, club, age)

FK Game(p1) references Participant(name),
FK Game(p2) references participant(name)

This is my relational schema and I am trying to return the list of names of participants who participated in the game but have not won...

So, I have

gathered all names,
removed who won from all names through not exists
removed all the draw names

BUT I AM UNABLE TO ELIMINATE THE PEOPLE WHO HAVE NOT PARTICIPATED IN THE GAME BUT ARE STILL IN THE name COLUMN OF PARTICIPANTS TABLE.

This part of my code is not working

{ 
SELECT name 
FROM Participant 
JOIN Game 
WHERE Participant.name!=Game.p1 
    OR Participant.name!=Game.p2
}

This is my complete code:

SELECT name 
FROM Participant P
WHERE name NOT IN (
    SELECT DISTINCT p1
    FROM   Game G
    WHERE  pointsp1 > pointsp2
    UNION ALL
    SELECT DISTINCT p2
    FROM   Game G
    WHERE  pointsp1 < pointsp2
    UNION ALL
    SELECT ender
    FROM   Game G
    WHERE  pointsp1 = pointsp2
    UNION ALL
    SELECT starter
    FROM   Game G
    WHERE  pointsp1 = pointsp2   
    UNION 
    SELECT name 
    FROM   Participant 
    JOIN   Game 
    WHERE  Participant.name!=Game.p1 
        OR Participant.name!=Game.p2
)

Possible duplicate of [Not in In SQL statement?](http://stackoverflow.com/questions/10132371/not-in-in-sql-statement) — Sam Franklin, Apr 24 '17 at 05:05

score 0 · Answer 1 · answered Apr 24 '17 at 04:15

Assuming you just want the list of participants who've played a game, but not won, try this.

select name
from participant
where name not in (
    select case 
      when g.pointsp1 < g.pointsp2 then p2
      when g.pointsp1 > g.pointsp2 then p1
      end as winner
    from game g
    where g.pointsp1 != g.pointsp2
)
  and name in (
    select p1 from game
    union all
    select p2 from game
)

The first sub-select gets all the winners (and ignores ties), the second gets all the participants across all games. This isn't a great way to accomplish the task, but it'll work.

score 0 · Answer 2 · answered Apr 24 '17 at 04:22

You can something like below.

SELECT name 
FROM Participant P
WHERE name NOT IN (
    SELECT DISTINCT p1
    FROM   Game G
    WHERE  pointsp1 > pointsp2
    UNION ALL
    SELECT DISTINCT p2
    FROM   Game G
    WHERE  pointsp1 < pointsp2
    UNION ALL
    SELECT ender
    FROM   Game G
    WHERE  pointsp1 = pointsp2
    UNION ALL
    SELECT starter
    FROM   Game G
    WHERE  pointsp1 = pointsp2)
OR  name not in (
    select p1 from game
    union all
    select p2 from game
)

How to use NOT IN in SQL?

2 Answers2