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

i have table 4 columns of user information. each table has following columns:

username | full_name | job_name | current_job_allowed 

table 1 includes users , job_name have permissions view. means there multiple lines of same username in table 1 different job_name values.

table 2 contains list of 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 users specified query parameter specify, i.e. 'kml_20160531'.

i select distinct list of users have different job_name parameter specify job_name. 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 return below when enter 20160531 job_name. return possible new users job_name value 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 

this parameter query show users have table 1 row job_name matching parameter value:

parameters which_job text ( 255 ); select t1.[username], t1.job_name [table 1] t1 t1.job_name=[which_job]; 

so can use subquery, left join table 2 subquery, , select rows "right side" null:

parameters which_job text ( 255 ); select t2.[username]     [table 2] t2     left join         (             select t1.[username]             [table 1] t1             t1.job_name=[which_job]         ) sub     on t2.[username] = sub.[username] sub.[username] null; 

assuming query returns correct rows, add other field want see select t2.[username].

you should not need distinct unless table 2 allows duplicate username values, or table 1 allows more 1 row same combination of username , job_name.