| Author |
 Topic  |
|
|
yoyosh
Starting Member
27 Posts |
 Posted - 02/11/2013 : 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 |
Edited by - yoyosh on 02/11/2013 02:24:23
|
|
|
LoztInSpace
Aged Yak Warrior
878 Posts |
Posted - 02/11/2013 : 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. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 02/11/2013 : 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/
|
|
|
|
yoyosh
Starting Member
27 Posts |
Posted - 02/11/2013 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 02/11/2013 : 05:54:45
|
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/
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 02/12/2013 : 05:46:04
|
Why are you storing comma seperated values in a column? Read about Normalisation
Madhivanan
Failing to plan is Planning to fail |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 02/12/2013 : 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. |
|
|
|
yoyosh
Starting Member
27 Posts |
Posted - 02/15/2013 : 01:15:49
|
| Thank you very much |
|
|
| |
Topic |
|
|
|
set numbers in set numbers
