SEARCH several Tables IN SQL

I'm trying to search several tables at once for a search term. My query is:

SELECT item.ItemID 
    FROM Inventory.Item item 
        JOIN Inventory.Category catR // each item can be in several categories
            ON catR.ItemID = item.ItemID 
        JOIN Category.Category cat 
            ON cat.CategoryID = catR.CategoryID 
        JOIN Inventory.Brand bran 
            ON bran.BrandID = item.BrandID
    WHERE
         item.Description LIKE '%' + @term + '%'
       OR
         item.Description LIKE '%' + @term
       OR
         item.Description LIKE @term + '%'
       OR
         item.Description = @term
       OR
         cat.CategoryName LIKE '%' + @term + '%'
             //same pattern as item.Description used to search CategoryName
             //...
       OR
         bran.BrandName LIKE '%' + @term + '%'
             //same pattern as item.Description used to search BrandName
             //...

But the results are not as expected. I have about 50 items in the category "Casement" but when term == "Casement" only items that have "Casement" in their item.Description will be returned.

Am I doing something wrong? Should I do this a better way?

Its enough to write

item.Description LIKE '%' + @term + '%'

instead of

item.Description LIKE '%' + @term + '%'
OR
   item.Description LIKE '%' + @term
OR
   item.Description LIKE @term + '%'
OR
   item.Description = @term

Conceptually I would keep it simple and then change it from there if needed for performance. First I would create a view and then do the select off of that.

CREATE VIEW vSearchTables
AS

SELECT item.ItemID, 'Item' AS TableName, item.Descripton AS Txt
FROM Inventory.Item item

UNION ALL

SELECT catR.ItemID, 'Category' AS TableName, cat.CategoryName AS Txt
FROM Inventory.Category catR 
JOIN Category.Category cat  
ON cat.CategoryID = catR.CategoryID 

UNION ALL

SELECT item.ItemID, 'Brand' AS TableName, bran.BrandName AS Txt
FROM Inventory.Item  item
JOIN Inventory.Brand bran
ON bran.BrandID = item.BrandID 

GO



SELECT ItemID
FROM vSearchTables
WHERE Txt LIKE '%'+@term +'%'


GO

If you have sql2005 and want to test this concept you can run the following:

CREATE VIEW vSearchTables 
AS 

select object_name(o.object_id) Object, o.type, m.definition as Txt
from sys.sql_modules m    
join sys.objects o on m.object_id = o.object_id    

GO


SELECT *
FROM vSearchTables 
WHERE Txt LIKE '%TRIGGER%' 

This is a good CTE example:

WITH items AS (
     SELECT i.itemid,
            i.description,
            cat.category_name,
            b.brandname 
       FROM INVENTORY.ITEM i 
  LEFT JOIN INVENTORY.CATEGORY c ON c.itemid = i.itemid
  LEFT JOIN CATEGORY.CATEGORY cat ON cat.CategoryID = c.categoryid
  LEFT JOIN INVENTORY.BRAND b ON b.brandid = i.brandid)
SELECT a.itemid
  FROM items a
 WHERE a.description LIKE '%' + @term + '%'
UNION ALL
SELECT b.itemid
  FROM items b
 WHERE b.categoryname LIKE '%' + @term + '%'
UNION ALL
SELECT c.itemid
  FROM items c
 WHERE c.brandname LIKE '%' + @term + '%'

CTEs are supported in SQL Server 2005+.

Try this:

Select i.ItemID    
From Inventory.Item i
  Join Inventory.Category ic
    On ic.ItemID = i.ItemID         
  Join Category.Category cc            
    On cat.CategoryID = ic.CategoryID         
  Join Inventory.Brand ib
    On ib.BrandID = i.BrandID
Where CharIndex(@Term,
      i.Description + '|' + 
     ic.Description + '|' +
     cc.Description + '|' +
     ib.Description) > 0

Realize that this will cause a full table scan of all four tables, so it will be slow if these tables are large. But given what you are trying to do, the only alternative would be to implement full text indices on the database...

Also, if it is possiblke that one of these tables does not contain a matching row for yr join condition, you should make all the joins outer joins, and use IsNull() on all the column references...

Select i.ItemID    
From Inventory.Item i
  Left Join Inventory.Category ic
    On ic.ItemID = i.ItemID         
  Left Join Category.Category cc            
    On cat.CategoryID = ic.CategoryID         
  Left Join Inventory.Brand ib
    On ib.BrandID = i.BrandID
Where CharIndex(@Term,
      i.Description + '|' + 
     IsNull(ic.Description, '') + '|' +
     IsNull(cc.Description, '') + '|' +
     IsNull(ib.Description, '')) > 0