Matching Values from Two Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Matching Values from Two Tables

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2011-03-11 : 08:45:53

Hello

I have two tables: VVData and MatchODF and I want to find the records where VVData.[Reference No] = MatchODF.OrderNo AND VVData.[UDO Balance] = MatchODF.UDO_Balance. The challenge for me is that sometimes the MatchODF.OrderNo value is formatted differently from the VVData.[Reference No] value. For example, VVData.[Reference No] = 'COW-2C-0011' AND MatchODF.UDO_Balance = 'COW2C0011'. I need to find the matches regardless if there are hyphens in either value.

My SQL here works only for those values with no hyphens. Thanks

SELECT id, [UDO Balance], REPLACE([reference no],'-','') AS REF_NO from vvdata
where [reference no] IN (SELECT REPLACE([order_no],'-','') FROM MatchODF)
AND [UDO Balance] IN (SELECT UDO_Balance FROM MatchODF)
order by [reference no]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-11 : 10:03:18

[code]SELECT id, [UDO Balance], REPLACE([reference no],'-','') AS REF_NO from vvdata
where REPLACE([reference no],'-','') IN (SELECT REPLACE([order_no],'-','') FROM MatchODF)
AND [UDO Balance] IN (SELECT UDO_Balance FROM MatchODF)
order by [reference no][/code]
If you have a large number of records in MatchODF, you will get better performance if you join the tables instead of using the IN clause.

evanburen
Posting Yak Master

167 Posts

Posted - 2011-03-11 : 10:46:38

That works! Thanks very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources