locked
Join two table and return the difference RRS feed

  • Question

  • Hi Everyone,

    I having a problem getting the result I want in an SQL query and need some help.  I try to used all the different type of join but can't accomplish it.  I would really appreciate all the help.

    Basically, I have two tables, one have a list of all the employees(table1) and a second table that contain a list of some of the employees.  I need to run a select join query between the two tables for DepartmentNO=1 and return any unmatch records.

    Thank you.


    • Edited by zXSwordXz Thursday, April 17, 2014 8:47 PM
    Thursday, April 17, 2014 8:46 PM

Answers

  • create table #t1 (empID int, FN varchar(50), LN varchar(50))
    create table #t2 (empID int, FN varchar(50), LN varchar(50))
    insert into #t1 values (1, 'bill', 'Gates'), (2, 'sam', 'cook'), (3, 'obama', 'Barack')
    insert into #t2 values (1, 'bill', 'Gates'), (2, 'sam', 'cook')
    
    
    select  #t1.FN,#t1.LN,#t1.empID
    from 
    #t1 left join #t2 on #t1.empID=#t2.empID 
    where #t2.empID is  null and  #t2.LN is  null and #t2.FN is  null 
    
    
    drop table #t1, #t2


    ebro

    • Proposed as answer by ebrolove Thursday, April 17, 2014 9:37 PM
    • Marked as answer by zXSwordXz Friday, April 18, 2014 1:21 AM
    Thursday, April 17, 2014 9:33 PM
  • Deleted
    • Marked as answer by zXSwordXz Friday, April 18, 2014 1:00 AM
    Thursday, April 17, 2014 8:56 PM

All replies

  • select * from Table1 where not exists (select 1 from Table2 where Table2.EmployeeID = Table1.EmployeeID and Table2.DepartmentNo = Table1.DepartmentNo)

    where Table1.DepartmentNo = @DepartmentNo;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Kalman Toth Wednesday, April 23, 2014 2:52 PM
    Thursday, April 17, 2014 8:55 PM
  • Deleted
    • Marked as answer by zXSwordXz Friday, April 18, 2014 1:00 AM
    Thursday, April 17, 2014 8:56 PM
  • select *
      from table1 t1
        full outer join table2 t2
          on t1.employeeID = t2.employeeID
     where t1.employeeID is null and t2.departmentNO = 1

       


    Thursday, April 17, 2014 9:25 PM
  • create table #t1 (empID int, FN varchar(50), LN varchar(50))
    create table #t2 (empID int, FN varchar(50), LN varchar(50))
    insert into #t1 values (1, 'bill', 'Gates'), (2, 'sam', 'cook'), (3, 'obama', 'Barack')
    insert into #t2 values (1, 'bill', 'Gates'), (2, 'sam', 'cook')
    
    
    select  #t1.FN,#t1.LN,#t1.empID
    from 
    #t1 left join #t2 on #t1.empID=#t2.empID 
    where #t2.empID is  null and  #t2.LN is  null and #t2.FN is  null 
    
    
    drop table #t1, #t2


    ebro

    • Proposed as answer by ebrolove Thursday, April 17, 2014 9:37 PM
    • Marked as answer by zXSwordXz Friday, April 18, 2014 1:21 AM
    Thursday, April 17, 2014 9:33 PM