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
 set numbers in set numbers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yoyosh
Starting Member

27 Posts

Posted - 02/11/2013 :  02:21:15  Show Profile  Reply with Quote
How to check whether the any of given numbers in set(eg. (1,2,3)) is equal to any number in another set (eg. 3,4,5)? I would like to use that in WHERE clause.

For example:
(1,2,3) vs (3,4,5) => true
(1,2,3) vs (5,6,7) => false

Thank you in advance for help

Edited by - yoyosh on 02/11/2013 02:24:23

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 02/11/2013 :  02:47:06  Show Profile  Reply with Quote
That's not a very clear question. How is your set stored? If in rows then you just use IN or INTERSECT (or a join I guess) and check if the resulting count is > 0. If in columns, you've got a bit more typing to do as you'll have to check each manually.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/11/2013 :  03:17:41  Show Profile  Reply with Quote
post some sample data as it exists on table and give what output you need out of them!

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

Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 02/11/2013 :  05:27:26  Show Profile  Reply with Quote
Ok, here it is. We have two tables with the following data:
_________________
TABLE A:
Id | Col1
-------------
1 | 2,3
2 | 2,6
3 | 1,7
_______________________
TABLE B:
Id | Col1
-------------
1 | 2
2 | 2,3
3 | 1,4

Example of query: get data from table A that "fits" row with id=2 from table B.

This row has values: {2,3} so from table A the following rows should be returned:
1 | 2,3 -> because at least one number from {2,3} is equal
2 | 2,6 -> because at least one number from {2,3} is equal

Please ask if this is still not clear to you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/11/2013 :  05:54:45  Show Profile  Reply with Quote

declare @TABLEA table
(
Id int, Col1 varchar(50)
)
insert @tableA
SELECT 1 , '2,3' union all
SELECT 2 , '2,6' union all
SELECT 3 , '1,7'

declare  @TABLEB table
(
Id int, Col1 varchar(50)
)
insert @tableB
SELECT 1 , '2' union all
SELECT 2 , '2,3' union all
SELECT 3 , '1,4'

select p.id,p.col1
from
(
select t.id,t.col1,m.n.value('.[1]','int') AS XMLData
from (SELECT id,Col1, CAST('<Node><Val>' + REPLACE(Col1,',','</Val><Val>') + '</Val></Node>' AS xml) AS XMLCol1 FROM @TABLEA) t
CROSS APPLY XMLCol1.nodes('/Node/Val')m(n)
)p
INNER JOIN
(
select t.id,m.n.value('.[1]','int') AS XMLData
from (SELECT id,Col1, CAST('<Node><Val>' + REPLACE(Col1,',','</Val><Val>') + '</Val></Node>' AS xml) AS XMLCol1 FROM @TABLEB) t
CROSS APPLY XMLCol1.nodes('/Node/Val')m(n)
WHERE id = 2
)q
ON q.XMLData = p.XMLData
GROUP BY p.id,p.col1


output
--------------------
id	col1
--------------------
1	2,3
2	2,6



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

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22777 Posts

Posted - 02/12/2013 :  05:46:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Why are you storing comma seperated values in a column? Read about Normalisation

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 02/12/2013 :  06:14:02  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
From this you can also get the entries that match.
This gets all the matches from the two tables but you can restrict it.

declare @TABLEA table
(
Id int, Col1 varchar(50)
)
insert @tableA
SELECT 1 , '2,3' union all
SELECT 2 , '2,6' union all
SELECT 3 , '1,7'

declare @TABLEB table
(
Id int, Col1 varchar(50)
)
insert @tableB
SELECT 1 , '2' union all
SELECT 2 , '2,3' union all
SELECT 3 , '1,4'

;with cte1a as (
select id, i = 1, j = charindex(',',col1)-1, col1 from @TABLEA
union all
select id, i = j+2, j = charindex(',',col1,j+2)-1, col1 from cte1a where j > 0
)
,cte1b as(select id, num=substring(col1,i, coalesce(nullif(j,-1),len(col1))) from cte1a)
,cte2a as (
select id, i = 1, j = charindex(',',col1)-1, col1 from @TABLEB
union all
select id, i = j+2, j = charindex(',',col1,j+2)-1, col1 from cte2a where j > 0
)
,cte2b as(select id, num=substring(col1,i, coalesce(nullif(j,-1),len(col1))) from cte2a)
select distinct t1id = t1.id, t2id = t2.id
from cte1b t1
join cte2b t2
on t1.num = t2.num

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 02/15/2013 :  01:15:49  Show Profile  Reply with Quote
Thank you very much
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