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
 set numbers in set numbers

Author  Topic 

yoyosh
Starting Member

27 Posts

Posted - 2013-02-11 : 02:21:15
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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-02-11 : 02:47:06
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

52326 Posts

Posted - 2013-02-11 : 03:17:41
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 - 2013-02-11 : 05:27:26
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

52326 Posts

Posted - 2013-02-11 : 05:54:45
[code]
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

[/code]

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

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-12 : 05:46:04
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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-02-12 : 06:14:02
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 - 2013-02-15 : 01:15:49
Thank you very much
Go to Top of Page
   

- Advertisement -