Query problem (wanted to fetch category and parent catefory)

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:

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,

since the limiting criterion (the WHERE condition) is applied to the product table, that’s the one i would start with in my FROM clause

also, it’s probably a good idea to include something from the product table in the SELECT clause :slight_smile:


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

if you want to count products instead of listing them, use COUNT(*) in the SELECT clause instead of the product title, and add a GROUP BY clause

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 :slight_smile:


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

well, i did suggest how to do that, didn’t i, or perhaps you missed that part? or maybe you’ve not done a COUNT query before?

well, you didn’t mention that originally, and that would require an ORDER BY clause, wouldn’t it

SELECT cat.name AS category
     , subcat.name AS subcategory
     , COUNT(*) AS products
  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%'
GROUP
    BY cat.name 
     , subcat.name 
ORDER
    BY cat.name 
     , subcat.name 

I tried query like this but it’s not working…

I wanted count only active items not all items. anyone can help me? thanks in advance…

SELECT cat.name AS category, cat.PK_ID AS catid, subcat.name AS subcategory, subcat.PK_ID AS subcatid, COUNT( *  ) AS products
		FROM product, active_days   
	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 active_days.PK_ID = product.FK_ACTIVE_DAY_ID and DATE_ADD( product.created_on, INTERVAL active_days.value > now() and 
		product.product_status = 0 and product.product_title LIKE '%p%' 
	GROUP
		BY cat.name, subcat.name

“is not working” is not a valid mysql error message

SELECT cat.name     AS category
     , cat.PK_ID    AS catid
     , subcat.name  AS subcategory
     , subcat.PK_ID AS subcatid
     , COUNT(*)     AS products
  FROM products
[COLOR=Blue]INNER
  JOIN active_days   
    ON active_days.PK_ID = product.FK_ACTIVE_DAY_ID
   AND product.created_on +
        INTERVAL active_days.value [COLOR=Red]DAY[/COLOR] 
         > CURRENT_DATE[/COLOR]
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_status = 0 
   AND product.product_title LIKE '%p%' 
GROUP
    BY cat.PK_ID 
     , subcat.PK_ID

:slight_smile: