Query problem (wanted to fetch category and parent category)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Query problem (wanted to fetch category and parent category)

Post 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,
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

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

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