CODEWITHSUNDEEP
sql
mcclosa
mcclosa

Reputation: 1455

Select Command Where Users id does not appear in another table

I have the following SQL query:

SELECT users.user_id,
       users.first_name,
       users.last_name,
       roles.role,
       roles.role_id,
       users.username,
       users.description,
       users_vs_teams.team_id,
       teams.team_name,
       teams.status
FROM users
     INNER JOIN roles ON users.role_id = roles.role_id
     INNER JOIN users_vs_teams ON users.user_id = users_vs_teams.user_id
     INNER JOIN teams ON users_vs_teams.team_id = teams.team_id
WHERE( users.role_id = 3 )

This query displays all users who have a user role of 3 and the team they have been assigned for a sprint due to their user_id appearing in the users_vs_teams table. However, I want to display the opposite of this where it displays those users who have a role of 3 but their user_id is not found in the users_vs_teams table.

Any help would be much appreciated.

Upvotes: 1

Views: 133

Answers (4)

Lorena Pita
Lorena Pita

Reputation: 1516

 SELECT users.user_id, users.first_name, users.last_name, roles.role, roles.role_id, users.username, users.description, users_vs_teams.team_id, teams.team_name, teams.status
 FROM users INNER JOIN roles ON users.role_id = roles.role_id 
            INNER JOIN users_vs_teams ON users.user_id <> users_vs_teams.user_id 
            INNER JOIN teams ON users_vs_teams.team_id = teams.team_id
 WHERE (users.role_id = 3)

Upvotes: 0

JBrooks
JBrooks

Reputation: 10013

SELECT  users.user_id, 
        users.first_name, 
        users.last_name, 
        roles.role, 
        roles.role_id, 
        users.username, 
        users.description
FROM    users 
        INNER JOIN roles ON users.role_id = roles.role_id 
        LEFT JOIN users_vs_teams ON users.user_id = users_vs_teams.user_id 
WHERE   users.role_id = 3 
        AND  users_vs_teams.user_id IS NULL

Upvotes: 1

japzdivino
japzdivino

Reputation: 1746

Use LEFT JOIN instead, and get all NULL id's in users_vs_teams table by adding AND users_vs_teams.user_id IS NULL in WHERE clause, with that you can get what you want.

 SELECT users.user_id,
        users.first_name,
        users.last_name,
        roles.role,
        roles.role_id,
        users.username,
        users.description,
        users_vs_teams.team_id,
        teams.team_name,
        teams.status
    FROM users
        INNER JOIN roles ON users.role_id = roles.role_id
        INNER JOIN teams ON users_vs_teams.team_id = teams.team_id
        LEFT JOIN users_vs_teams ON users.user_id = users_vs_teams.user_id
    WHERE( users.role_id = 3 )
    AND users_vs_teams.user_id IS NULL

Upvotes: 0

Adish
Adish

Reputation: 691

Change to left outer join and add another condition to where clause. A left outer join fetches all rows from the table on the left side (here users) and the condition makes sure only those rows come up where there is no suitable match in the table on the right (here users_vs_teams). In the following query, you will have to change "pk" in the additional where clause condition to the actual column which is the primary key of users_vs_teams table.

SELECT  users.user_id, users.first_name, users.last_name, roles.role, roles.role_id, users.username, users.description, users_vs_teams.team_id, teams.team_name, teams.status
FROM    users 
        INNER JOIN roles ON users.role_id = roles.role_id 
        INNER JOIN teams ON users_vs_teams.team_id = teams.team_id
        LEFT OUTER JOIN users_vs_teams ON users.user_id = users_vs_teams.user_id 
WHERE   (users.role_id = 3)
        AND users_vs_teams.pk IS NULL

Upvotes: 0

Related Questions