October 28, 2007 at 3:00 am
Hello all,
Please help me out on comparing the data from two tables as following:
I have two tables having the same records. They may contain duplicated records. For example.
TableA
ID | Name | PurchaseDate | Amount
1 | John Smith | 01/02/2006 | 200.00
2 | Julie Lee | 09/30/2006 | 4500.00
3 | John Smith | 04/23/2007 | 900.00
4 | Brian Hellman | 01/01/2000 | 892.00
I added one colum name on tableA and corrected some money in Amount column that show following:
TableB
ID | Name | PurchaseDate | Amount | Location
8 | John Smith | 01/02/2006 | 555.00 | A
9| Julie Lee | 09/30/2006 | 3333.00 | B
11 | John Smith | 04/23/2007 | 900.00 | A
30 | Brian Hellman | 01/01/2000 | 888.00 | C
Now I would like to compare the data on Amount columns from both above tables and obtain new third table with the same records with two previous tables as bellowed
Name | AmountA | AmountB
John Smith |200.00 | 555.00
Julie Lee |4500.00 | 3333.00
John Smith |900.00| 900.00
Brian Hellman | 892.00| 888.00
I try:
SELECT tableA.Name, tableA.Amount as AmountA, tableB.Amount as AmountB
FROM tableA, tableB
WHERE tableA.Name = tableB.Name
but this above TSQL returns more records than I expect because they have duplicated Name key. Any help in TSQL is really appreciated. Thanks in advance.
October 28, 2007 at 3:48 am
So you want to get amounts from each table given a name and a day to match them?
You just need to adjust your join criteria slightly..
[font="Courier New"]SELECT tableA.Name, tableA.Amount as AmountA, tableB.Amount as AmountB
FROM tableA, tableB
WHERE tableA.Name = tableB.Name
AND tableA.PurchaseDate = tableB.PurchaseDate[/font]
You should also get into the habit of using INNER JOIN rather than your old-school join syntax (FROM with commas).
If you might have data in tableA that's not in tableB, or vice-versa (or both), and you still want this data in your resultset, you can use an OUTER JOIN.
Example...
[font="Courier New"]SELECT tableA.Name, tableA.Amount as AmountA, tableB.Amount as AmountB
FROM tableA
FULL OUTER JOIN tableB
ON tableA.Name = tableB.Name
AND tableA.PurchaseDate = tableB.PurchaseDate[/font]
Alternatively you could use LEFT joins or RIGHT joins to include data in tableA that's not in B (or vice-versa).
If I've misunderstood your requirements please restate them another way.
October 28, 2007 at 8:43 am
Hi,
Thanks for your response quickly. What if two tables having the same PurchaseDate? This is sample data only, but my actual large data got the same PurchaseDate. I want to clear more on data that tableB data having the same records with tableA and just updated the Amount column in tableB and added the new Location column. I would like to know whether any other SQL syntax that compare two tables line by line by order or not? Any advice would appreciate. Thanks.
October 28, 2007 at 8:52 am
you could also use the new "intersect" and "except" clauses to get mutual of different rows in a set ( unjoined ).
http://msdn2.microsoft.com/en-us/library/ms188055.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 29, 2007 at 4:27 am
SQL Server doesn't really do data comparisons in the literal sense. For that, you're better served by looking into third party tools like Red Gate's SQL Data Compare. There are other tools out there besides Red Gate, but I don't know the names since my company actually uses Red Gate for this particular function.
Oh, wait. Before I submit this post, I'd best point out SSIS. You can actually do Fuzzy Grouping / Lookup on your two different tables and do the compare that way. Look for stuff with a 90% match or better. That way, assuming you have Enterprise version of SQL Server, you don't have to spend additional funds on a third party tool you may only need to use once or twice.
Also, the way you're doing your join is going to depreciated soon, at least the OUTER JOIN version of it. I found this out by running Profiler. There's an event class called Depreciation that helps you locate code that is "out of sync" with Microsoft's current standards. And it will let you know about every possible piece of code that is or soon will be depreciated.
So correct your joins to the new INNER JOIN / OUTER JOIN ... ON version of T-SQL or when MS changes the standards (and they'll do it without warning because they've already warned us) your code will break spectacularly.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply