MySQL count rows in a joint table

Question

I would like to count the number of rows or in this case the number of products which are in the same category and return zero if there are no products in the category.

The tables in my query looking like this:

category                    category_lang                 media
------------                -------------                 ---------
category_id | published     category_id | name | alias    media_id | category_id
-----------------------     --------------------------    ----------------------
1            | 1            1           | One  | one      1        | 1
2            | 1            2           | Two  | two      2        | 2
3            | 1            3           | Three| three    3        | 3

media_lang                          product_category
-------------------------------     ----------------
media_id | url      | file_name     product_id | category_id
-------------------------------     ------------------------
1        | /images/ | file1.jpg     1          | 1
2        | /images/ | file2.jpg     2          | 1
3        | /images/ | file3.jpg     3          | 2

and I would like a result like this:

category_id | category_name | alias | media_id | url      | file_name | count
1           | One           | one   | 1        | /images/ | file1.jpg | 2
2           | Two           | two   | 2        | /images/ | file2.jpg | 1
3           | Three         | three | 3        | /images/ | file3.jpg | 0

My query currently looks like this

SELECT 
c.`category_id`, 
ca.`name`, 
ca.`alias`, 
m.`media_id`, 
ma.`url`, 
ma.`file_name`,
COUNT(p.`product_id`) AS `count`
FROM `category` c
LEFT JOIN `category_lang` ca ON (c.`category_id` = ca.`category_id`)
LEFT JOIN `media` m ON (c.`category_id` = m.`category_id`)
LEFT JOIN `media_lang` ma ON (m.`media_id` = ma.`media_id`)
LEFT JOIN `product_category` p ON (c.`category_id` = p.`category_id`)
WHERE c.`published` = 1
ORDER BY ca.`name`

my logic is obviously wrong because this query will return this:

category_id | category_name | alias | media_id | url      | file_name | count
1           | One           | one   | 1        | /images/ | file1.jpg | 3

How can I achieve the desired result?

Answer 1

Seems to me that it is where you are doing the count and that you doing have the criteria for doing the count. Adding an extra condition where product category id equals category id in your category table should fix the incorrect count.

SELECT 
c.`category_id`, 
ca.`name`, 
ca.`alias`, 
m.`media_id`, 
ma.`url`, 
ma.`file_name`,
COUNT(p.`product_id`) AS `count`
FROM `category` c
LEFT JOIN `category_lang` ca ON (c.`category_id` = ca.`category_id`)
LEFT JOIN `media` m ON (c.`category_id` = m.`category_id`)
LEFT JOIN `media_lang` ma ON (m.`media_id` = ma.`media_id`)
LEFT JOIN `product_category` p ON (c.`category_id` = p.`category_id`)
WHERE (c.`published` = 1) and (c.`category_id` = p.`category_id`)
ORDER BY ca.`name`

Answer 2

I think the most painless (and sane) way to approach this would be to just aggregate the product counts for each category from the product_category table in a separate subquery, and then just join this to what you already have:

SELECT 
    c.category_id, 
    ca.name, 
    ca.alias, 
    m.media_id, 
    ma.url, 
    ma.file_name,
    COALESCE(t.cat_count, 0) AS cat_count
FROM category c
LEFT JOIN category_lang ca
    ON c.category_id = ca.category_id
LEFT JOIN media m
    ON c.category_id = m.category_id
LEFT JOIN media_lang ma
    ON m.media_id = ma.media_id
LEFT JOIN
(
    SELECT category_id, COUNT(*) AS cat_count
    FROM product_category
    GROUP BY category_id
) t
    ON c.category_id = t.category_id
WHERE c.published = 1
ORDER BY ca.name

Note here that your product_category table does not have any category entries with no products. This is not a problem, because in the LEFT JOIN we can simply treat a NULL count as being zero. A NULL value would occur if a given category did not match to anything in the subquery.