Query to compare two views and find non-matching record ??

• sestell1

  SSChampion

  Points: 10230

  More actions

  May 16, 2016 at 8:10 am

  #1879255

  Both of those queries work because they are essentially the same thing.

  You should probably work to understand those queries so that you can modify them to return the columns that you want.

  Here is my solution (which also works), modified to return the distinct employee numbers:

  SELECT

  DISTINCT IsNull(Employee.[Emp_no.], Class.[Emp_no.])

  FROM

  #Employee Employee

  FULL OUTER JOIN

  #Class Class

  ON

  IsNull(Employee.[Emp_no.], '') = IsNull(Class.[Emp_no.], '')

  ANDIsNull(Employee.Org_Code, '') = IsNull(Class.Org_Code, '')

  ANDIsNull(Employee.Org_Num, '') = IsNull(Class.Org_Num, '')

  WHERE

  (

  Employee.[Emp_no.] IS NULL

  AND Employee.Org_Code IS NULL

  AND Employee.Org_Num IS NULL

  )

  OR

  (

  CLASS.[Emp_no.] IS NULL

  AND CLASS.Org_Code IS NULL

  AND CLASS.Org_Num IS NULL

  )

• rk1980factor

  SSCommitted

  Points: 1681

  More actions

  May 16, 2016 at 10:26 am

  #1879311

  Below query gives error message: Incorrect Syntax near 'NULL' on Last line of the code

  SELECT

  DISTINCT IsNull(Employee.[Emp_no], Class.[Emp_no])

  FROM

  Employee

  FULL OUTER JOIN

  Class

  ON

  IsNull(Employee.[Emp_no], '') = IsNull(Class.[Emp_no], '')

  ANDIsNull(Employee.Org_Code, '') = IsNull(Class.Org_Code, '')

  ANDIsNull(Employee.Org_Num, '') = IsNull(Class.Org_Num, '')

  WHERE

  (

  Employee.[Emp_no] IS NULL

  AND Employee.Org_Code IS NULL

  AND Employee.Org_Num IS NULL

  )

  OR

  (

  CLASS.[Emp_no] IS NULL

  AND CLASS.Org_Code IS NULL

  AND CLASS.Org_Num IS NULL

• sestell1

  SSChampion

  Points: 10230

  More actions

  May 16, 2016 at 11:32 am

  #1879326

  That is because you are missing the last parenthesis.