title: compare comma separated value with column collection of a table
body: <p>One table <code>X</code> is having a column <code>C1</code> and having value with comma separated, i.e., 1,2,3</p>
  
  <p>Another table <code>Y</code> is having a column <code>C2</code> with unique <code>tinyint</code> value and having multiple rows and the column <code>C2</code> values are, i.e., </p>
  
  <pre><code>1
  2
  3
  4
  5
  </code></pre>
  
  <p>Requirement: Check is all the values of <code>X(C1)</code> are exist in the <code>Y(C2)</code> table
  Tried One: </p>
  
  <pre><code>((select Data from dbo.split(X.C1,',')) in  ((Select C2 from Y where &lt;some condition&gt;))) 
  </code></pre>
  
  <p>Where Split is a user defined function which splits based on 'Comma' and put it into individual rows of a table and returns that table, i.e.,
  <code>Split(X.C1,',')</code> returns a table with multiple rows like</p>
  
  <pre><code>1
  2
  3
  </code></pre>
  
  <p>But, using this query is giving run time error:</p>
  
  <blockquote>
    <p>Subquery returned more than 1 value. This is not permitted when the
    subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as
    an expression.</p>
  </blockquote>
  
  <p>Could any one please help to get more feasible solution</p>
  
  <p>Thanks in Advance
  Kiran Bussa</p>