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 

     &nbsp

    and not exists (select 1 from tblCurrentWinTrainingLog c

      where o.EMPLNO=c.EMPLNO

      AND o.quizname = @quiz 

     &nbsp

    Replace the smileys with Close Parentheses - I havent figured out how to turn those off.

     


    I feel the need - the need for speed

    CK Bhatia

  • 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 )

  • "Select 1.." basically means that you are not interested in fetching any columns, but only want to check whether a row exists for the specified where clause.

     


    I feel the need - the need for speed

    CK Bhatia

  • 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