How can I Count in multiple table?

I have some problem to get the ID which use on another table row. I have three tables. One is category, articles, video. In articles and video there is a column which have category ID. This is the example:

Table Categories :

id | category_name
------------------
1 | News
2 | Sports
3 | Art
4 | Horror


Table Articles :

id | category_id | title
----------------------------------
1 | 1 | title content 1
2 | 1 | title content 2
3 | 3 | title content 3
4 | 3 | title content 4
5 | 2 | title content 5


Table Video :

id | category_id | video_title
------------------------------
1 | 1 | video title 1
2 | 2 | video title 2
3 | 3 | video title 3


I want to get each category ID already use what time in two other databases. Like this :

Category ID 1 is use 3 times
Category ID 2 is use 2 times
Category ID 3 is use 3 times
Category ID 4 is use 0 times


What query do I need to use so I can get all data like that ? Please anyone knows could help me. Thanks before.

2 Answers
2

First you need to UNION ALL articles table and video table be a subquery, then use Outer join and COUNT function.

UNION ALL

articles

video

Outer join

COUNT

SELECT Concat('Category ID ', c.id, ' is use ', Count(t.category_id ), ' times')
FROM categories c
LEFT JOIN (SELECT category_id
FROM articles
UNION ALL
SELECT category_id
FROM video) t
ON c.id = t.category_id
GROUP BY c.id


SQLFIDDLE:http://sqlfiddle.com/#!9/92cbd0e/12

[Results]:

| Concat('Category ID ', t.id, ' is use ', t.cnt, ' times') |
|------------------------------------------------------------|
| Category ID 1 is use 3 times |
| Category ID 2 is use 2 times |
| Category ID 3 is use 3 times |
| Category ID 4 is use 0 times |


NOTE

COUNT

null

For example Here is a sample script.

CREATE TABLE T(
col int
);

INSERT INTO T VALUES (NULL);
INSERT INTO T VALUES (1);

SELECT COUNT(col) FROM t; -- RESULT = 1

SELECT COUNT(*) FROM t; --RESULT = 2


sample sqlfiddle:http://sqlfiddle.com/#!9/e2bba7/2

You can use, for example, this query:

Select category_id, count(1) - 1
From (
Select category_id From video
Union All Select category_id From articles
Union All Select id From Categories)
Group By category_id


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.