Listing common data in multiple tables belonging to a category table with MySQL

Question

i have 3 tables. this-> category, blog, course

category table

| category_id | category_name |
|      1      |  php          |
|      2      |  python       |
|      3      |  javascript   |

blog table

| blog_id | blog_title | blog_category_id |
|    1    | trial 1    |         1        |
|    2    | trial 2    |         2        |
|    3    | trial 3    |         1        |
|    4    | trial 4    |         2        |
|    5    | trial 5    |         3        |
|    6    | trial 6    |         1        |
|    7    | trial 7    |         3        |

course table

| course_id | course_title | course_category_id |
|    1      | trial 1      |         1          |
|    2      | trial 2      |         3          |
|    3      | trial 3      |         2          |
|    4      | trial 4      |         1          |
|    5      | trial 5      |         1          |
|    6      | trial 6      |         1          |
|    7      | trial 7      |         3          |

output:

| category_name | TotalCount |
|    php        |     7      |
|    python     |     3      |
|    javascript |     4      |

how can i list such output MySQL. pleaseee....

score 1 · Accepted Answer · answered Jun 19 '21 at 00:36

Get all the category IDs from the blog and course tables with a UNION ALL then JOIN that back to the category table to get the category names. GROUP BY the category_id to count the totals.

The result looks like this:

SELECT COUNT(category.category_id), category_name FROM category 
    JOIN (
      SELECT blog_category_id AS category_id FROM blog 
      UNION ALL 
      SELECT course_category_id AS category_id FROM course
    ) a ON 
    (category.category_id = a.category_id)
 GROUP BY category.category_id;

Listing common data in multiple tables belonging to a category table with MySQL

1 Answers1