SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 join between two tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amurark
Yak Posting Veteran

India
53 Posts

Posted - 12/20/2012 :  09:44:59  Show Profile  Reply with Quote
Hi Pls tell me
There are two tables A and B where asset tags are present, but in one table in rows and in another in column wise.

for eg
ASSet Tag
SR-062009-00032966
SR-062009-00032962
SR-072009-00020572
SR-072009-00020571
SR-072009-00020585
HH-092009-00038342




Table B
Asset TAG
SR-072009-00020572,SR-072009-00020571,SR-062009-00020685,SR-072009-00001592,SR-072009-00001376,SR-062009-00020683,SR-092009-00001617


pls tell me the query so that asset tag of A matches with each and every asset tag table of B and output comes as

Output

SR-062009-00032966
SR-062009-00032962
SR-072009-00020572
SR-072009-00020571
SR-072009-00020585


Ankita

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/20/2012 :  11:01:11  Show Profile  Reply with Quote
two methods

1. using string comparison technique

SELECT a.*
FROM tableA a
INNER JOIN tableB b
WHERE ',' + b.AssetTAG + ',' LIKE '%,' + a.AssetTAG + ',%'

2. use a string splitting UDF and then join
SELECT
FROM
(
SELECT f.Val
FROM tableB b
CROSS APPLY dbo.ParseValues(b.AssetTAG,',') f
)m
INNER JOIN TableA a
ON a.AssetTAG = m.Val


ParseValues can be found in below link

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000