I wanted to fetch category name and parent category name against products search, I also wanted to count products in category found against a search term.

Here is my query:

select c.parentid, c.PK_ID from category c, product WHERE product.product_status = 0 and ( product.product_title LIKE '%p%' ) and c.PK_ID = product.FK_CATEGORY_ID

product table:
PK_ID, product_title
Category table:
parentid, PK_ID, name [Note: parentid tells who is parent of that category]