SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot dizyn's Avatar
    Join Date
    Apr 2006
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot dizyn's Avatar
    Join Date
    Apr 2006
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 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%'

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dizyn View Post
    ...and i also wanted to fetch number of items found in sub categories.
    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?

    Quote Originally Posted by dizyn View Post
    ... 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
    well, you didn't mention that originally, and that would require an ORDER BY clause, wouldn't it

    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot dizyn's Avatar
    Join Date
    Apr 2006
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "is not working" is not a valid mysql error message

    Code:
    SELECT cat.name     AS category
         , cat.PK_ID    AS catid
         , subcat.name  AS subcategory
         , subcat.PK_ID AS subcatid
         , COUNT(*)     AS products
      FROM products
    INNER
      JOIN active_days   
        ON active_days.PK_ID = product.FK_ACTIVE_DAY_ID
       AND product.created_on +
            INTERVAL active_days.value DAY 
             > CURRENT_DATE
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •