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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Comma separated Join Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tooba
Posting Yak Master

195 Posts

Posted - 05/28/2013 :  14:02:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/28/2013 :  14:04:07  Show Profile  Reply with Quote
What should be the expected output of the given data?

Cheers
MIK
Go to Top of Page

tooba
Posting Yak Master

195 Posts

Posted - 05/28/2013 :  14:11:30  Show Profile  Reply with Quote
The OutPut Should be

ID, Codes
3, 90
1, 897
2, 487
Go to Top of Page

tooba
Posting Yak Master

195 Posts

Posted - 05/28/2013 :  14:20:56  Show Profile  Reply with Quote
Updated Output


The Out Put Should be

ID, Codes
3, 90
1, 123,897
2, 487,98
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/28/2013 :  14:36:24  Show Profile  Reply with Quote
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
Go to Top of Page

tooba
Posting Yak Master

195 Posts

Posted - 05/28/2013 :  14:41:12  Show Profile  Reply with Quote
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?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/28/2013 :  14:51:03  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/28/2013 :  14:52:33  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/28/2013 :  14:53:43  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/28/2013 :  15:02:17  Show Profile  Reply with Quote
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. :)
Go to Top of Page

tooba
Posting Yak Master

195 Posts

Posted - 05/28/2013 :  16:32:12  Show Profile  Reply with Quote
Thank You, Quick question. How i can Split values in other table and then join it.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/28/2013 :  18:07:31  Show Profile  Reply with Quote
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?
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.06 seconds. Powered By: Snitz Forums 2000