title: sql server complex query - fetch data and search on multiple tables
body: <p>I have a two tables as follows:</p>
  
  <pre>
  <b>product category(t1):</b>
  id
  name
  </pre>
  
  <pre>
  <b>product master(t2):</b>
  id 
  name
  category
  </pre>
  
  <p>Now I have the following query to fetch the products and the associated category name in one query only:</p>
  
  <pre><code>select 
    *,
    (select name from t1 where t1.id=t2.category) as 'category' 
  from 
    t2
  </code></pre>
  
  <p>which is working perfectly. Now what I need to do is, perform filter operations on the result set. Like consider the following:</p>
  
  <pre><code>select 
    *,
    (select name from t1 where t1.id=t2.category) as 'category' 
  from 
    t2 
  where 
    t2.name like '%keyword%' or 'category' like '%keyword%'
  </code></pre>
  
  <p>now this is working only when i am entering a keyword which is on the name column but the search is not being performed based on the category name. Any ideas what I am doing wrong?</p>
  
  <p>BTW: I am using ASP.NET 3.5 along with SQL Server 2005 Express Edition.</p>
  
  <p>Appreciate your time and support. Many thanks in advance.</p>