Join Question

[FranS37]

Can I join on two fields (from two different tables), and find matches, where the value in one field is, for instance 10015, and the other is 10,015. Is there a way to construct an INNER JOIN to handle that?

Thanks.

 

[gmmastros]

Yes. You can do this. Please be aware that using functions in a join condition will slow things down quite a bit. With small tables, you'll probably never notice the performance problems, but as your tables get larger, it will become more noticeable.

Anyway... I assume one (or both) of the datatypes for the columns is varchar since you have commas in the data. You can construct the join like this...

Select Columns....
From   Table1
       Inner Join Table2
         On Replace(Table1.ColumnA, ',', '') = Replace(Table2.ColumnB, ',', '')

Note that I am replacing the commas prior to joining the data. If one (or the other) columns in an integer, then don't do the replace on that column. You only need the replace on the column that is a string.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Please do not misunderstand what I've done. I am replacing the commas with an empty string for the purpose of the join condition. This code is not permanently replacing or modifying any data within the table(s).


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom