CODEWITHSUNDEEP
mysqlsql
user3615331
user3615331

Reputation: 29

SQL (Mysql) NOT IN in combination with OR, wrong results

I have two tables:

tournament_teams:

ID TEAM TournamentID
2  Berp 7
3  Dang 7

tournament_pool_team:

ID TournamentID PoolID TeamID VersusTeamID
1  7            5      2      3

Now this query should return nothing since all teams are in the pool (teamid and versusteamid)

SELECT t1.id,
       t1.team
FROM   tournament_teams t1
WHERE  t1.id NOT IN(SELECT t2.id
                    FROM   tournament_pool_team t2
                    WHERE  ( t2.teamid = t1.id
                              OR t2.versusteamid = t1.id )
                           AND poolid = '5')
       AND t1.tournamentid = '7'
ORDER  BY team ASC

I only want to show teams that do not exist in the table: Tournament_pool_team; Teams are specified in teamid and versusteamid of that table. It needs to pull the teams from the tournament_teams who arent specified in tournament_pool_team. With the current entries, it should not return anything since the IDs both 2 and 3 are specified in tournament_pool_team.

Upvotes: 0

Views: 267

Answers (3)

DRapp
DRapp

Reputation: 48169

Yet another option... start with the teams list and do a left-join into the tournament trying against both teams and only keeping the teams where the JOIN results are null

SELECT 
      t1.id,
      t1.team
   FROM
      tournament_teams t1
         LEFT JOIN tournament_pool_team t2 
               t2.poolid = '5'
           AND t1.TournamentID = t2.TournamentID
           AND ( t1.id = t2.id
               OR t1.id = t2.versusteamid )
   where
      t1.tournamentid = '7'
      AND t2.id IS NULL
   ORDER  BY 
      team

I would have indexes on

tournament_teams on ( TournamentID, ID )
tournament_pool_team on ( TournamentID, poolid )

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93744

Your sub query returns tournament_pool_team's ID which is not present in tournament_teams id so you are getting wrong result.

Use Not Exists operator which will help you to find the tournament_teams which is not present in tournament_pool_team's TeamID or VersusTeamID

SELECT t1.id,
       t1.team
FROM   tournament_teams t1
WHERE  NOT EXISTS (SELECT 1
                   FROM   tournament_pool_team t2
                   WHERE  ( t2.teamid = t1.id
                             OR t2.versusteamid = t1.id )
                          AND t2.poolid = '5')
       AND t1.tournamentid = '7'
ORDER  BY t1.team ASC

Upvotes: 1

varun
varun

Reputation: 1523

SELECT t1.id,
       t1.team
FROM   tournament_teams t1
WHERE  t1.id NOT IN(SELECT t2.teamid
                    FROM   tournament_pool_team t2
                    WHERE  t2.teamid = t1.id
                    AND poolid = '5' 
                    UNION
                    SELECT t2.versusteamid
                    FROM   tournament_pool_team t2   
                    WHERE  t2.versusteamid = t1.id
                    AND poolid = '5')
       AND t1.tournamentid = '7'
ORDER  BY team ASC

I have modified the query according to your context(the way you were trying to implement). You were matching the t1.teamid with t2.id, instead you should be matching t1.id with t2.teamid and t2.versusteamid

Upvotes: 1

Related Questions