SQL Matching Query Question

[cojiro]

I have two tables from unrelated systems. The only common field for the tables is an amount field. I would like to return a result set of which amounts match, without repeating any match. For example:

Table A Table B
ID Amount ID Amount
---------------- ------------------
1 50.00 1 25.00
2 25.00 2 25.00
3 50.00 3 50.00
4 75.00 4 100.00
5 150.00 5 75.00

Desired Return Set:
A.ID A.Amount B.ID B.Amount
--------------------------------
1 50.00 3 50.00
2 25.00 1 25.00
4 75.00 5 75.00

I can't think of a clean way to do this with SQL. It would be nice if I could. So is this possible, or am I going to have to write a program for this?

Thanks for your help.

 

[kaht]

All you need is an inner join. And, to make it match your desired output, pull the min([ID]) instead of all [ID]s:

[COLOR=green]--declare the temp tables
[/color][COLOR=blue]declare[/color] @arr [COLOR=blue]table[/color] ([ID] [COLOR=blue]tinyint[/color], Amount [COLOR=blue]decimal[/color](5,2))
[COLOR=blue]declare[/color] @tee [COLOR=blue]table[/color] ([ID] [COLOR=blue]tinyint[/color], Amount [COLOR=blue]decimal[/color](5,2))

[COLOR=green]--populate first table
[/color][COLOR=blue]insert[/color] [COLOR=blue]into[/color] @arr
[COLOR=blue]select[/color] 1, 50 union
[COLOR=blue]select[/color] 2, 25 union
[COLOR=blue]select[/color] 3, 50 union
[COLOR=blue]select[/color] 4, 75 union
[COLOR=blue]select[/color] 5, 150

[COLOR=green]--populate second table
[/color][COLOR=blue]insert[/color] [COLOR=blue]into[/color] @tee
[COLOR=blue]select[/color] 1, 25 union
[COLOR=blue]select[/color] 2, 25 union
[COLOR=blue]select[/color] 3, 50 union
[COLOR=blue]select[/color] 4, 100 union
[COLOR=blue]select[/color] 5, 75

[COLOR=green]--double check the contents
[/color][COLOR=blue]select[/color] * [COLOR=blue]from[/color] @arr
[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @tee

[COLOR=green]--find the matches
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]min[/color](a.[ID]), 
   a.Amount,
   [COLOR=#FF00FF]min[/color](b.[ID]),
   b.Amount
[COLOR=blue]from[/color] @arr a
   [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @tee b
   [COLOR=blue]on[/color] a.Amount = b.Amount
[COLOR=blue]group[/color] [COLOR=blue]by[/color] a.Amount, b.Amount
[COLOR=blue]order[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]min[/color](a.[ID])

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[tcsbiz]

In your example, you don't want to know that you have two $50.00 amounts in Table A and two $25.00 amounts in Table B?
It seems to me that you would want to know that.

 

[AlexCuse]

A better question is, what exactly is this result set supposed to mean?

It seems to me you might be trying to get square data from a round table.

Good Luck,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[cojiro]

kaht,
That method does work for the data set mentioned above, but the problem comes in a situation where there are 2 or more similar totals in both tables. I would like to return a row for each successful match, rather than just one.

tcsbiz/AlexCuse,
The reason why i have these requirements is because of this business reason: the amounts from table A are from a bank that doesn't have our customer information, where table B is the result of a customer account being updated (with these bank amounts). So this is more a less a reseach tool to see which bank amounts were succefully applied to a customer account. This type of matching wouldn't be perfect, but it would be a starting point for research.

 

[gmmastros]

This is a super bad idea. In fact... it's the worst idea I've ever heard.

Please, please, please don't tell me it's for my bank? Please.

There is nothing more sacred in a database than the integrity of the data. What you are (apparently) trying to do is fit a round table in a square peg. It just doesn't work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cojiro]

Yeah, I know this is a bad idea, but sometimes you have to work with what you're given. Thank you all for your help.

 

[gmmastros]

I get that. I really do. But sometimes you have to stand up and say, "I can't do that based on the data I am given". In this case, that would be the right thing to do.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom