How to select all records from one table which are not already selected by user?

Question

I know there exist a similar question How to select all records from one table that do not exist in another table? but i have a different scenario

I have a TotalTrainings that consist of all records

TraningCode |  TraningName
1                   A
2                   B
3                   C
4                   D
5                   E

and another table SelectedTraining

TraningCode |  TraningName | EmpCode | AppraisalID
1                   A           7190      12
2                   B           7190      12
3                   C           8132      10
4                   D           5555      08
5                   E           8132      10

Now i had to retrieve all records(trainings) from TotalTrainings which are not selected by EmpCode = 7190 having appraisalId 12

Note alias of TotalTraining is tt and SelectedTraining is st

I have done written query like this :

select tt.trainingCode from TotalTraining tt left join SelectedTraining st
on tt.trainingCode = st.trainingCode where st.trainingCode is null 
and EmpCode=7190 and appraisalId =12

but it is giving no result .

I have achieved the same with NOT IN and it is perfectly working

select tt.trainingCode from TotalTraining tt where tt.trainingCode not in 
(
select st.trainingCode from SelectedTraining st where 
EmpCode=7190 and appraisalId =12
)

I want to achieve with Joins , Can anyone tell me where i might be wrong.

Note : Kindly Ignore Typos here such as case sensitive AppraisalId and appraisalId because that is not the problem

Expected output is

3
4
5

because these training till now not have been selected by EmpCode 7190

score 2 · Accepted Answer · answered Feb 11 '15 at 17:48

For your first query, you need to push most of the conditions to the on clause -- all the conditions on the second table:

select tt.trainingCode
from TotalTraining tt left join
     SelectedTraining st
     on tt.trainingCode = st.trainingCode and
        st.EmpCode = 7190 and
        st.appraisalId = 12
where st.trainingCode is null ;

How to select all records from one table which are not already selected by user?

1 Answers1