-
Sep 3rd, 2012, 07:17 AM
#1
Thread Starter
Hyperactive Member
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.
-
Sep 3rd, 2012, 07:35 AM
#2
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
-
Sep 3rd, 2012, 08:17 AM
#3
Thread Starter
Hyperactive Member
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)
-
Sep 4th, 2012, 03:41 AM
#4
Thread Starter
Hyperactive Member
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"
-
Sep 7th, 2012, 04:24 PM
#5
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
-
Sep 10th, 2012, 03:32 AM
#6
Thread Starter
Hyperactive Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|