Comma separated Join Help - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Comma separated Join Help

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2013-05-28 : 14:02:01

Hi guys i have question.

Here is my Table1 (Sample Table)

ID, Codes
1, 123,897
2, 487,98
3, 90
4, 12

Table2

ID, SampleCode
1, 11
2, 90,121
3, 897
4, 487

I want to create a Inner Join Between Table1.Codes And Table2.SampleCode
Any Help?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-28 : 14:04:07

What should be the expected output of the given data?

Cheers
MIK

tooba
Posting Yak Master

224 Posts

Posted - 2013-05-28 : 14:11:30

The OutPut Should be

ID, Codes
3, 90
1, 897
2, 487

tooba
Posting Yak Master

224 Posts

Posted - 2013-05-28 : 14:20:56

Updated Output

The Out Put Should be

ID, Codes
3, 90
1, 123,897
2, 487,98

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 14:36:24

sounds like this to me


SELECT ID,Codes
FROM table1 t1
WHERE EXISTS (SELECT 1
FROM Table2
WHERE (',' + t1.Codes + ',' LIKE '%,' + SampleCode + ',%'
OR ',' + SampleCode + ',' LIKE '%,' + t1.Codes + ',%')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tooba
Posting Yak Master

224 Posts

Posted - 2013-05-28 : 14:41:12

This is Part of Long SP. I have to use Inner Join,

Could you please take a look below Inner Join

inner join table2 t2 on
CONVERT(VARCHAR,','+replace(replace(t2.Codes,'.',''),' ','')+',') = CONVERT(VARCHAR,','+replace(replace(t1.SampleCodes,'.',''),' ','')+',')

Any advise?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-28 : 14:51:03

The real answer is you should fix your data (Look up normalization). But, here is some code that'll work:

DECLARE @Table1 TABLE (ID INT, Codes VARCHAR(100))

INSERT @Table1 (ID, Codes)
VALUES
(1, '123,897'),
(2, '487,98'),
(3, '90'),
(4, '12')

DECLARE @Table2 TABLE (ID INT, SampleCodes VARCHAR(100))
INSERT @Table2 (ID, SampleCodes)
VALUES
(1, '11'),
(2, '90,121'),
(3, '897'),
(4, '487')

-- Split function from:
-- http://www.sqlservercentral.com/articles/T-SQL/62867/
--
SELECT
	T1.*
FROM 
	@Table1 AS T1
OUTER APPLY
	(
		SELECT Item
		FROM dbo.DelimitedSplit8K(Codes, ',')
	) AS D1
INNER JOIN
	@Table2 AS T2
OUTER APPLY
	(
		SELECT Item
		FROM dbo.DelimitedSplit8K(SampleCodes, ',')
	) AS D2
	ON D1.Item = D2.Item

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 14:52:33

putting a concatenation logic like this can have very bad effect on query performance especially when tables are large
In that case it might be worth splitting values out to temporary tables and then join using them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 14:53:43

Lamprey...You just read my mind

Was too lazy to post the query...Its been a loong day..thanks for posting it!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-28 : 15:02:17

quote:
Originally posted by visakh16

Lamprey...You just read my mind
Was too lazy to post the query...Its been a loong day..thanks for posting it!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

My pleasure. :)

tooba
Posting Yak Master

224 Posts

Posted - 2013-05-28 : 16:32:12

Thank You, Quick question. How i can Split values in other table and then join it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-28 : 18:07:31

quote:
Originally posted by tooba

Thank You, Quick question. How i can Split values in other table and then join it.

Same as the example above? Or is there something different about the other table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources