join between two tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

join between two tables

Author

Topic

amurark
Yak Posting Veteran

55 Posts

Posted - 2012-12-20 : 09:44:59

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

52326 Posts

Posted - 2012-12-20 : 11:01:11

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources