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.
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]
Thank you for the solution but I wanted to fetch categories and sub categories only and i also wanted to fetch number of items found in sub categories.
Following code shows all the products and categories and sub categories but the problem is that i wanted to fetch it in a way so that i can display it like categories and then it's sub categories and then category and it's sub categories etc
SELECT product.product_title
, subcat.name AS subcategory
, cat.name AS category
FROM product
INNER
JOIN category AS subcat
ON subcat.pk_id = product.fk_category_id
INNER
JOIN category AS cat
ON cat.pk_id = subcat.parentid
WHERE product.product_title LIKE '%p%'