Results 1 to 6 of 6

Thread: Comparing two tables

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    350

    Comparing two tables

    I have a query 'Query1' which gives a list on pay_codes & names,
    and another query 'Query2' which gives a slightly different list of pay_codes & names.

    I want to know which pay_code & names are in one table but not in the other.

    Searching, I have found the following.

    query = "SELECT MIN(TableName) as TableName, pay_code, name" & _
    " FROM (" & query1 & " UNION ALL " & query2 & ") AS tmp" & _
    " GROUP BY pay_code, name HAVING COUNT(*) = 1 ORDER BY pay_code"

    This gives:
    Table B, A002, name1
    Table B, A079, name2
    Table A, A085, name3
    Table A, A095, name4

    It gives a list of the names in query1 but not in query2, AND the names in query2 but not in query1.
    I only want:
    Table B, A002, name1
    Table B, A079, name2

    If I add "WHERE TableName = 'Table B'" I get the whole table.

    How can I get hjust the names I want?
    Thanks.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Comparing two tables

    Try this:
    Code:
    SELECT TableB.pay_code, TableB.name
    FROM TableB 
    LEFT JOIN TableA ON (TableA.pay_code = TableB.pay_code AND TableA.name = TableB.name)
    WHERE TableA.pay_code Is Null

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    350

    Re: Comparing two tables

    My actual code was:

    "SELECT MIN(TableName) as TableName, pay_code, name FROM (SELECT DISTINCT 'TableA' as TableName, pay_code, name FROM ViewJobsheetEmp WHERE (week_end >='12/26/2009' AND week_end <='01/01/2010') AND LEFT(pay_code,1)='A' UNION ALL SELECT 'TableB' as TableName, pay_code, name FROM employee WHERE left_company='FALSE' AND LEFT(pay_code,1)='A' AND (start_date<='01/01/2010' AND (end_date>='12/26/2009' OR end_date='01/01/1900'))) AS tmp GROUP BY pay_code, name HAVING COUNT(*) = 1 ORDER BY pay_code"

    This gave the differences.

    I have changed it to:

    "SELECT DISTINCT vjs.pay_code AS vjsP, vjs.name AS vjsN, e.pay_code AS eP, e.name AS eN FROM ViewJobsheetEmp AS vjs" & _
    " LEFT JOIN Employee AS e ON (vjs.pay_code = e.pay_code)" & _
    " WHERE (vjs.week_end >='" & StartDate & "' AND vjs.week_end <='" & EndDate & "')" & _
    " AND LEFT(vjs.pay_code,1)='A'" & _
    " AND e.left_company='FALSE' AND LEFT(e.pay_code,1)='A'" & _
    " AND (e.start_date<='" & EndDate & "' AND (e.end_date>='" & StartDate & "' OR e.end_date='01/01/1900'))"

    But it just gives a list of the pay_codes (& names in both tables)

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    350

    Re: Comparing two tables

    I was able to fix it by making a query on the results of the previous one.
    I think I am now understanding about nested queries - remember to use brackets and use an alias.

    SourceQuery = "SELECT MIN(TableName) as TableName, pay_code, name" & _
    " FROM (" & query1 & " UNION ALL " & query2 & ") AS tmp" & _
    " GROUP BY pay_code, name HAVING COUNT(*) = 1"

    query = "SELECT pay_code, name FROM (" & SourceQuery & ") AS sq WHERE TableName='TableB' ORDER BY pay_code"

  5. #5
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Re: Comparing two tables

    Is this access or sql server? in Sql server, you can use the except operator to get what you want.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    350

    Re: Comparing two tables

    Thanks, it is SQL, I will have to give that a try.

Posting Permissions

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



Click Here to Expand Forum to Full Width