MySQL (# of Products in Category) COUNT() w/ LEFT JOIN & ON 2 Tables

Question

I am trying to resolve getting a count of all products in each category. Products are uniquely identified by PRODUCT_ID. The PRD_PRODUCTS table does not have a CATEGORY_ID field, but all products are joined in a join table PRD_CATEGORIES on PRODUCT_ID and CATEGORY_ID.

I have 2 tables:

PRD_PRODUCTS and PRD_CATEGORIES.

PRD_PRODUCTS
------------
PRODUCT_ID  - varchar(64)
TITLE - varchar(64)

PRD_CATEGORIES
--------------
CATEGORY_ID - varchar(64)
PRODUCT_ID - varchar(64)
TITLE - varchar(64)

Example data:
PRODUCT_ID: 5e313ec3bf7b967569eec196b4693829 (unique ID assigned to each product)
TITLE: Product Name One (product name)
PARENT_CATEGORY_ID: (this joins with CATEGORY_ID)
CATEGORY_ID: 10 (unique identifier for each category)

This is what I am currently working with, and just getting error after error. Last error saying PRODUCT_ID field does not exist, but I am certain it is exactly there. Hope to get a new set of eyes on this...

$db->query="SELECT count(a.PRODUCT_ID), 

    a.PRODUCT_ID,
    a.TITLE 

    FROM 

    PRD_PRODUCTS AS a, 
    PRD_CATEGORIES AS b

    LEFT JOIN PRD_PRODUCTS ON a.PRODUCT_ID = b.PRODUCT_ID

    LEFT JOIN PRD_CATEGORIES ON b.CATEGORY_ID = c.CATEGORY_ID 

WHERE a.SHOW_ON = 'A' 

GROUP BY a.PRODUCT_ID

ORDER BY a.TITLE DESC";

Answer 1

Aren't you just looking for something like this?

SELECT CATEGORY_ID, COUNT(PRODUCT_ID) AS product_count
  FROM PRD_CATEGORIES
  GROUP BY CATEGORY_ID

UPDATE with filter:

SELECT pc.CATEGORY_ID, COUNT(pc.PRODUCT_ID) AS product_count
  FROM PRD_CATEGORIES pc
      ,PRD_PRODUCTS p
  WHERE pc.PRODUCT_ID = p.PRODUCT_ID
    AND p.SHOW_ON = 'A'
  GROUP BY CATEGORY_ID

Answer 2

No, he needs to join the two tables together to know which products are in which category. I'm not sure why the original code joins twice though? If I understand what he is trying to do then he should just join the product id's to the categories (or vice versa) and then take the count of distinct product ids... right?

Also the group by needs to change to group by category since that is what you are trying to sum by...

SELECT 
     b.TITLE 
    ,count(distinct a.PRODUCT_ID)
 from PRD_PRODUCTS a
 left join  PRD_CATEGORIES b on b ON a.PRODUCT_ID = b.PRODUCT_ID
GROUP BY b.TITLE