Page 1 of 1

Query problem (wanted to fetch category and parent category)

Posted: Sat Mar 27, 2010 5:54 am
by itsmani1
Hi,

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:

Code: Select all

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]

thanks,

Re: Query problem (wanted to fetch category and parent categ

Posted: Mon Mar 29, 2010 2:21 am
by itsmani1
Hi,

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

Thanks for the help :)

Code: Select all

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%'