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) => falseThank 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. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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,32 | 2,63 | 1,7_______________________TABLE B:Id | Col1-------------1 | 22 | 2,33 | 1,4Example 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 equal2 | 2,6 -> because at least one number from {2,3} is equalPlease ask if this is still not clear to you. |
|
|
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 @tableASELECT 1 , '2,3' union allSELECT 2 , '2,6' union allSELECT 3 , '1,7'declare @TABLEB table(Id int, Col1 varchar(50))insert @tableBSELECT 1 , '2' union allSELECT 2 , '2,3' union allSELECT 3 , '1,4'select p.id,p.col1from(select t.id,t.col1,m.n.value('.[1]','int') AS XMLDatafrom (SELECT id,Col1, CAST('<Node><Val>' + REPLACE(Col1,',','</Val><Val>') + '</Val></Node>' AS xml) AS XMLCol1 FROM @TABLEA) tCROSS APPLY XMLCol1.nodes('/Node/Val')m(n))pINNER JOIN(select t.id,m.n.value('.[1]','int') AS XMLDatafrom (SELECT id,Col1, CAST('<Node><Val>' + REPLACE(Col1,',','</Val><Val>') + '</Val></Node>' AS xml) AS XMLCol1 FROM @TABLEB) tCROSS APPLY XMLCol1.nodes('/Node/Val')m(n)WHERE id = 2)qON q.XMLData = p.XMLDataGROUP BY p.id,p.col1output--------------------id col1--------------------1 2,32 2,6[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 NormalisationMadhivananFailing to plan is Planning to fail |
|
|
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 @tableASELECT 1 , '2,3' union allSELECT 2 , '2,6' union allSELECT 3 , '1,7'declare @TABLEB table(Id int, Col1 varchar(50))insert @tableBSELECT 1 , '2' union allSELECT 2 , '2,3' union allSELECT 3 , '1,4';with cte1a as (select id, i = 1, j = charindex(',',col1)-1, col1 from @TABLEAunion allselect 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 @TABLEBunion allselect 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.idfrom cte1b t1join cte2b t2on 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 - 2013-02-15 : 01:15:49
|
Thank you very much |
|
|
|
|
|