Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
tsql question

Answered tsql question

  • Sunday, February 17, 2013 4:57 PM
     
     
    Sign In to Vote
    0

    hi,

    I have a problem.

    i have a table for example with two these columns : personnelID,Date

    in other table I have these 2 fields :

    t1 : PersonnelID     Date                            t2:   PersonnelId       Date

              1                 2/17/2013                               3                    2/17/2012

              1                 2/15/2013                                1                   2/17/2013

              2                 3/02/2013

    I want a query that returns every row from t1 that not exists in t2

    the result must be like this :

    result : personnelid date

                 1                2/15/2013

                 2               3/02/2013

          the with this value :  1      2/17/2013 has been omitted.

    I tried some ways but It doesn't work.I need some comparison based on personnelid And Date.

    thanks.

     

All Replies

  • Sunday, February 17, 2013 5:16 PM
     
     Answered Has Code
    Sign In to Vote
    0

    This is an outer left-join:

    SELECT T1.*
FROM T1
  LEFT JOIN T2 ON T1.PersonnelID = T2.PersonnelID AND T1.[Date] = T2.[Date]
WHERE T2.PersonnelID IS NULL;

    • Marked As Answer by princess_980 Sunday, February 17, 2013 5:32 PM
    •  
     
  • Sunday, February 17, 2013 5:32 PM
     
     
    Sign In to Vote
    0

    thanks alot.it works good.I tried left join but forgot : where t2.personnelid is null