CODEWITHSUNDEEP
mysql
Shaikh Kamran Ahmed
Shaikh Kamran Ahmed

Reputation: 360

mysql get COUNT in many to many relationship

I have created a many to many relationship in my database.

promotions table:

+----+---------+-----------+------------+-----------+------------+--------------------+
| id | city    | title     | highlights | details   | conditions | url                |
+----+---------+-----------+------------+-----------+------------+--------------------+
| 1  | Toronto | Test Data | Test Data  | Test Data | Test Data  | https://google.com |
+----+---------+-----------+------------+-----------+------------+--------------------+
| 2  | Toronto | Test Data | Test Data  | Test Data | Test Data  | https://google.com |
+----+---------+-----------+------------+-----------+------------+--------------------+
| 3  | Toronto | Test Data | Test Data  | Test Data | Test Data  | https://google.com |
+----+---------+-----------+------------+-----------+------------+--------------------+
| 4  | Toronto | Test Data | Test Data  | Test Data | Test Data  | https://google.com |
+----+---------+-----------+------------+-----------+------------+--------------------+
| 5  | Toronto | Test Data | Test Data  | Test Data | Test Data  | https://google.com |
+----+---------+-----------+------------+-----------+------------+--------------------+

category table:

+----+------------+-----------+-----------------------+------------+
| id | name       | parent_id | image_url             | sort_order |
+----+------------+-----------+-----------------------+------------+
| 1  | Category 1 | 0         | https://fakeimage.url | 0          |
+----+------------+-----------+-----------------------+------------+
| 2  | Category 2 | 0         | https://fakeimage.url | 0          |
+----+------------+-----------+-----------------------+------------+
| 3  | Category 3 | 0         | https://fakeimage.url | 0          |
+----+------------+-----------+-----------------------+------------+
| 4  | Category 4 | 0         | https://fakeimage.url | 0          |
+----+------------+-----------+-----------------------+------------+
| 5  | Category 5 | 0         | https://fakeimage.url | 0          |
+----+------------+-----------+-----------------------+------------+

promo_cat table:

+--------------+-------------+
| promotion_id | category_id |
+--------------+-------------+
| 1            | 1           |
+--------------+-------------+
| 2            | 1           |
+--------------+-------------+
| 3            | 2           |
+--------------+-------------+
| 4            | 3           |
+--------------+-------------+
| 5            | 4           |
+--------------+-------------+

What I want to achieve here to count the number of promotions in each category. Before I had category_id inside promotions table so I could access the count using the below query

SELECT
`c`.`name`,
`c`.`id`,
`c`.`image_url`,
`c`.`parent_id`,
`c`.`sort_order`,
`cat`.`name` AS `parent_category_name`,
(
SELECT
    COUNT(*)
FROM
    `promotions`
WHERE
    `promotions`.`category_id` = `c`.`id` AND `promotions`.`city` = "Toronto"
) AS COUNT
FROM
    `category` c
LEFT JOIN `category` cat ON
    `cat`.`id` = `c`.`parent_id`
GROUP BY
    `c`.`id`
ORDER BY
    COUNT

I have tried with many different queries but failed last query which I tried was using a sub query to fetch count but it also returns zero. I have pasted my last query below as well.

SELECT
    category.name,
    category.id,
    category.image_url,
    category.parent_id,
    category.sort_order,
    (
    SELECT
        COUNT(*)
    FROM
        `promotions`
    JOIN promo_cat ON promo_cat.promotion_id = promotions.id
    JOIN category ON category.id = promo_cat.category_id
    WHERE
        promo_cat.category_id = category.id AND promotions.city = "Toronto"
) AS COUNT
FROM
    promotions
JOIN promo_cat ON promo_cat.promotion_id = promotions.id
JOIN category ON category.id = promo_cat.category_id
GROUP BY
    category.id

SQL Fiddle with sample data and tables http://sqlfiddle.com/#!9/5b94228

Upvotes: 0

Views: 122

Answers (1)

El_Vanja
El_Vanja

Reputation: 3983

There is no need for a separate query just to get the count. It can all be done directly in the main query with a proper connection between the tables and grouping:

SELECT category.*, COUNT(*) AS no_of_promotions
FROM category
INNER JOIN promo_cat ON promo_cat.category_id = category.id
INNER JOIN promotions ON promo_cat.promotion_id = promotions.id
GROUP BY category.id;

I've placed the category table in the FROM clause (since category data is the basis of your selection) and then joined it with promotions through the promo_cat lookup table, but it would work even if the order of the tables was reversed (select from promotions > join promo_cat > join category).

Since you want to observe records per category, the data is grouped by the category id. Now simply adding a count in such a query will return the number of records per group.

Upvotes: 1

Related Questions