sql server complex query - fetch data and search on multiple tables

Question

I have a two tables as follows:

product category(t1):
id
name

product master(t2):
id 
name
category

Now I have the following query to fetch the products and the associated category name in one query only:

select 
  *,
  (select name from t1 where t1.id=t2.category) as 'category' 
from 
  t2

which is working perfectly. Now what I need to do is, perform filter operations on the result set. Like consider the following:

select 
  *,
  (select name from t1 where t1.id=t2.category) as 'category' 
from 
  t2 
where 
  t2.name like '%keyword%' or 'category' like '%keyword%'

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?

BTW: I am using ASP.NET 3.5 along with SQL Server 2005 Express Edition.

Appreciate your time and support. Many thanks in advance.

Answer 1

JOINs are your friend :)

SELECT t2.*, t1.name
FROM t2
    JOIN t1 ON t2.category = t1.id
WHERE t2.name LIKE '%keyword%'
     OR t1.name LIKE '%keyword%'