title: Matching multiple rows in where clause for filter
body: <p>I have two tables as the below:</p>
  <pre><code>Table 1 : Product_Information 
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">Information_ID</th>
  <th style="text-align: center;">Product_Name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">A</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">B</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">C</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">D</td>
  </tr>
  <tr>
  <td style="text-align: center;">5</td>
  <td style="text-align: center;">E</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code>Table 2 : Discriptor_Values 
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">Information_ID</th>
  <th style="text-align: center;">Descriptor_ID</th>
  <th style="text-align: center;">Descriptor_Value</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">98</td>
  </tr>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">142</td>
  </tr>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">29.66</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">50</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">11</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">14</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">17</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">76</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">85</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">59</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">48</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">35</td>
  </tr>
  <tr>
  <td style="text-align: center;">5</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">48</td>
  </tr>
  <tr>
  <td style="text-align: center;">5</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">12</td>
  </tr>
  <tr>
  <td style="text-align: center;">5</td>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">19</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Using the above tables, I am creating a filter page like in any online shopping page i.e. for mobile phone Min and max range of price, Min and max range of internal storage are descriptor and range of values.
  Likewise I will select descriptor and give min and max values for it and the matching product will be the result.
  If I pass any filter range then the filtered list of products will be shown else all the records should be shown.</p>
  <p>I am trying as the below query but not getting the correct output. I am getting the union of rows which matches any of the passed row (#tblFilter ).</p>
  <pre><code>CREATE TABLE  #tblFilter(
      [descriptor_id] [int] NULL,
      [min_value] [decimal](18, 0) NULL,
      [max_value] [decimal](18, 0) NULL
  )
  
  insert into #tblFilter values (1, 40.33, 70.33)
  insert into #tblFilter values (2, 100.33, 150.33)
  insert into #tblFilter values (3, 10, 60)
  
  
      select p.*
          from Product_Information p
              inner join Discriptor_Values dv on p.Information_ID = dv.Information_ID
              left join #tblFilter t1 on t1.descriptor_id = dv.Descriptor_id
          WHERE ((dv.Descriptor_ID = t1.descriptor_id
              and convert(decimal, dv.Descriptor_Value)
                  between CONVERT(decimal, t1.min_value) and CONVERT(decimal, t1.max_value))
         or not exists (select 1 from #tblFilter))
  
  
  drop TABLE  #tblFilter  
  </code></pre>
  <p>Please help me to minimize the result list by filter and show all records if there is no row in filter table (#tblFilter).</p>