select record not in one table in another table
-
I need to get the employee list which in the Occhemp table and not in tblCurrentWinTrainingLog in the
tblPreviousWinTrainingLog during specific time
I
set @sql='select distinct o.EMPLNO ,[FULL NAME] , [Unit],[Unit Desc],Manager, Department, JobCCNo
from dbo.OcchEmp o inner join tblCurrentWinTrainingLog p on o.EMPLNO = p.[EmplNO] left join tblCurrentWinTrainingLog c on p.EMPLNO=c.EMPLNO where c.EMPLNO is null '
set @sql= @sql+ ' AND p.quizname = '''+ @quiz +''''
i do not know how to get the employee in the tblPreviousWinTrainingLog .
Thx.
-
If you need to get the columns only from the OcchEmp table, then you can use something like the following sql statement without resorting to dynamic SQL. I am assuming that you have declared the @quiz variable.
select o.EMPLNO ,[FULL NAME] , [Unit],[Unit Desc],Manager, Department, JobCCNo
from dbo.OcchEmp o
where exists (select 1 from tblCurrentWinTrainingLog p
where o.EMPLNO = p.[EmplNO]
AND p.quizname = @quiz
 
and not exists (select 1 from tblCurrentWinTrainingLog c
where o.EMPLNO=c.EMPLNO
AND o.quizname = @quiz
 
Replace the smileys with Close Parentheses - I havent figured out how to turn those off.
-
Thanks. I tested. it work fine. but what select 1 here mean.
select o.EMPLNO ,[FULL NAME] , [Unit],[Unit Desc],Manager, Department, JobCCNo
from dbo.OcchEmp o
where exists (select 1 from tblPreviousWinTrainingLog p
where o.EMPLNO = p.[EmplNO]
AND p.quizname = 'Slips and Falls' )
and not exists (select 1 from tblCurrentWinTrainingLog c
where o.EMPLNO=c.EMPLNO )
-
I got lost; I tried to modifed the stored procedure I have add some criteria.
I do not know how to accomplish that. the blue part is what I do not know how to do.
The employee might have a few records in the tblPreviousWinTrainingLog . I just want to select the most recent one.
I want to get the due list which one year later which the employee took the quiz.
ig:
emplno quizname ctdate
1234 ABC 12/05/06
1234 ABC 11/0/06
1234 CDE 12/05/06
1235 CDE 12/05/06
the user want to extract the ABC quiz who due in the 12/01/07 and 12/15/07.
then I want the result as emplno 1234 . if the user does not select any date or quiz name. so all the employee will get select without record in the corresponding quiz in the tblCurrentWinTrainingLog table. Thx.
ALTER PROCEDURE dbo.DueListWithPreviousTraining
@quiz VARCHAR( 25 ),
@fromdate datetime = NULL,
@todate datetime = NULL,
@unit nchar(5) = NULL,
@cc nvarchar(125) = NULL,
@debug bit = 0
as
DECLARE @sql nvarchar(4000)
begin
set @sql='select o.EMPLNO ,[FULL NAME] , [Unit],[Unit Desc],Manager, Department, JobCCNo
from dbo.OcchEmp o where exists (select 1 from tblPreviousWinTrainingLog p
where o.EMPLNO = p.[EmplNO]
IF @quiz IS NOT NULL
set @sql= @sql+ ' AND p.quizname = ' + @quiz +' ) and
not exists (select 1 from tblCurrentWinTrainingLog c where o.EMPLNO=c.EMPLNO and
AND p.quizname = ' + @quiz +' )'
else
set @sql= 'select o.EMPLNO ,[FULL NAME] , [Unit],[Unit Desc],Manager, Department, JobCCNo
from dbo.OcchEmp o where exists (select 1 from tblPreviousWinTrainingLog p
where o.EMPLNO = p.[EmplNO] ) and not exists (select 1 from tblCurrentWinTrainingLog c
where o.EMPLNO=c.EMPLNO )'
IF @fromdate IS NOT NULL
set @fromdate= DATEADD(year,-1, @fromdate)
set @sql = @sql + ' AND p.CTDate >= ''' + convert(char(13), @fromdate,112) + ''''
IF @todate IS NOT NULL
set @todate= DATEADD(year,-1, @todate)
set @sql = @sql + ' and p.CTDate <= ''' + convert(char(13), @todate,112) + ''''
IF @Debug = 1
BEGIN
PRINT @sql PRINT ''
END
exec (@sql)
end
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply