Tiaan Smal Tiaan Smal - 1 year ago 48
SQL Question

Showing results from a select statement when PK isnt referenced in another table

I have 2 database tables, one containing user information and another containing the specific corporate roles they are performing.

Some of the fields contained in these DB's are as follows:

Table1: UserInformation

Username, name, surname, RoleID (FK Reference to Table2) etc

Table2: RoleInformation
ID (PK and ref from Table 1 RoleID), RoleName etc

The program:

I have a dropdown which I am populating with the results of a SQL query.
The program I am writing will be used by HR to assign unused company roles to selected company users in order to kick off other processes within the company.

I am very unexperienced thus far with SQL only being able to write very little more complicated than an extra long insert statement lol
Can someone help me structure a SQL query that returns roles that are not currently assigned to a user.

In pseudocode:

select RoleName
from Table2
where the ID is not referenced/used in Table1

I hope I described the scenario correctly...

Thanks in advance!!

Answer Source

I think you're looking for NOT EXISTS or NOT IN

select RoleName 
  from Table2 t2
  where not exists (select 1 
                      from Table1 t1 
                     where t1.RoleID = t2.RoleInformationID);