Comparing two tables

[DavidGraham167]

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.

[si_the_geek]

Try this:

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

[DavidGraham167]

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)

[DavidGraham167]

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"

[abhijit]

Is this access or sql server? in Sql server, you can use the except operator to get what you want.

[DavidGraham167]

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