Conrad Addo Conrad Addo - 4 months ago 6
SQL Question

SELECT DISTINCT Users in Table 1 which don't exist in Table 2

I have a table with 4 columns of user information. Each table has the following columns:

Username | Full_Name | Job_Name | Current_Job_Allowed


Table 1 includes all users and the Job_Name which they have permissions to view. This means that there are multiple lines of the same username in Table 1 with different Job_Name values.

Table 2 contains a list of all possible users.

Username |Full_Name
--------------+-----------------
amunoz |Andrew Munoz
csmith |Carl Smith
cwatkins |Cat Watkins
ggriffiths |Garmin Griffiths
jcarr |Jason Carr
jhothi |Jark Hothi
jphillips |Jim Phillips
lbradfield |Lisa Bradfield
ntaylor |Noria Taylor
rfelipe |Ralf Felipe


Query 1 contains all users specified by a query parameter which I specify, i.e. 'KML_20160531'.

I would like to now select a DISTINCT list of all users which have a different Job_Name from the parameter I specify for Job_Name. For example Table 1 contains:

Username|Full_Name |Job_Name |Current_Job_Allowed
--------+------------+------------+----------------------
amunoz |Andrew Munoz|KML_20160531|1
jcarr |Jason Carr |KML_20160531|1
rfelipe |Ralf Felipe |KML_20140531|1
amunoz |Andrew Munoz|KML_20160431|1


I would then like to return the below when I enter 20160531 for Job_Name. This will return all possible new users for the Job_Name value I entered.

Username |Full_Name
--------------+---------------
csmith |Carl Smith
cwatkins |Cat Watkins
ggriffiths |Garmin Griffiths
jhothi |Jark Hothi
jphillips |Jim Phillips
lbradfield |Lisa Bradfield
ntaylor |Noria Taylor
rfelipe |Ralf Felipe

Answer

This parameter query will show you which users have a Table 1 row with Job_Name matching the parameter value:

PARAMETERS which_job Text ( 255 );
SELECT t1.[Username], t1.Job_Name
FROM [Table 1] AS t1
WHERE t1.Job_Name=[which_job];

So you can use that as a subquery, left join Table 2 to the subquery, and select the rows where the "right side" is Null:

PARAMETERS which_job Text ( 255 );
SELECT t2.[Username]
FROM
    [Table 2] AS t2
    LEFT JOIN
        (
            SELECT t1.[Username]
            FROM [Table 1] AS t1
            WHERE t1.Job_Name=[which_job]
        ) AS sub
    ON t2.[Username] = sub.[Username]
WHERE sub.[Username] Is Null;

Assuming that query returns the correct rows, add the other field you want to see to SELECT t2.[Username].

You should not need DISTINCT unless Table 2 allows duplicate Username values, or Table 1 allows more than one row with the same combination of Username and Job_Name.