CODEWITHSUNDEEP
phpmysqlsql
somejkuser
somejkuser

Reputation: 9040

SQL NOT IN operator not working correctly

I have the following tables:

        industries
        -----------
        + id
        + name

        users
        -----------
        + id
        + name

        teams
        -----------
        + id
        + name
        + type

        user_to_team
        -----------
        + id
        + user_id
        + team_id
        + industry_id

I am running the following sql query:

        SELECT teams.id
        FROM teams 
        LEFT JOIN user_to_team ON user_to_team.`team_id` = teams.id 
        WHERE teams.type = 'general' 
        AND (user_to_team.industry_id NOT IN(1))
        GROUP BY user_to_team.team_id 
        LIMIT 1

The problem is that it's returning teams that contain an industry_id of 1 in the user_to_team table.

Upvotes: 0

Views: 170

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

You can use LEFT JOIN / IS NULL

SELECT teams.id
FROM teams 
  LEFT JOIN user_to_team 
    ON  user_to_team.team_id = teams.id
    AND user_to_team.industry_id IN (1)    -- caution: this is IN
                                           -- and **not** NOT IN
WHERE teams.type = 'general' 
  AND user_to_team IS NULL ;

or a NOT EXISTS subquery, which is easier to read:

SELECT teams.id
FROM teams 
WHERE teams.type = 'general' 
  AND NOT EXISTS
      ( SELECT *
        FROM user_to_team 
        WHERE user_to_team.team_id = teams.id 
          AND user_to_team.industry_id IN (1)       -- caution: this is IN 
                                                    -- and **not** NOT IN
      ) ;

Upvotes: 0

AgRizzo
AgRizzo

Reputation: 5271

This assumes that any given team can be associated with more than 1 industry in the user_to_team table.

SELECT DISTINCT teams.team_id
FROM teams
LEFT JOIN user_to_team
  ON user_to_team.team_id = teams.team_id
    AND user_to_team.industry_id = 1
WHERE teams.type = 'general'
  AND user_to_team.team_id IS NULL;

Upvotes: 0

Erutan409
Erutan409

Reputation: 752

Have you tried moving that conditional check into the 'LEFT JOIN' statement for that table, after the 'ON' clause?

SELECT teams.id
FROM teams
LEFT JOIN user_to_team ON user_to_team.`team_id` = teams.id
AND user_to_team.industry_id NOT IN(1)
WHERE teams.type = 'general'
LIMIT 1

Also, do you have the correct data type for that column?

Upvotes: 1

Related Questions