Comparing data

Results 1 to 3 of 3

Thread: Comparing data

  1. #1
    Join Date
    Dec 1969

    Default Comparing data

    I have 2 (Access) tables : one for products and one for departements.<BR><BR>I&#039;d like to compare the &#039;DepartementNumber&#039; field in both tables and report the records that don&#039;t match.<BR><BR>i.e. : If a product from the (...of course) "Product" table has a given &#039;DepartementNumber&#039; that does not exists in the "Departement" table , then Report it.<BR>Thanks<BR><BR>Don

  2. #2
    Join Date
    Dec 1969

    Default RE: Comparing data

    select * <BR>from products<BR>where departmentNumber != products.departmetNumber

  3. #3
    Join Date
    Dec 1969

    Default Not quite...

    That would find NO RECORDS, ever, since it is looking in the same table for both departmentNumber values!<BR><BR>You need a join, either implicitly or explicitly. <BR><BR>Such as:<BR><BR>SELECT products.* <BR>FROM products, departments<BR>WHERE products.DepartmentNumber &#060;&#062; departments.DepartmentNumber<BR><BR>(You can use either &#060;&#062; or != with Access, to mean "not equal.")<BR><BR>Another way to do that:<BR><BR>SELECT products.* <BR>FROM products<BR>WHERE DepartmentNumber NOT IN (<BR>&nbsp; &nbsp; SELECT DepartmentNumber FROM Departments<BR>&nbsp; &nbsp; )<BR><BR>I&#039;ve heard that the JOIN way (the first way) is more efficient. Seems to me that a *good* query optimizer ought to turn both of them into the same thing, under the covers. But...<BR><BR><BR>Incidentally, "Department" is the correct spelling. There is no "e" after the first "t".<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts