Geek asks and answers

Get everything from one table and COUNT from another

K, so I have two tables:

categories
+----+----------+
| id | slug     |
+----+----------+
| 1  | billing  |
| 2  | security |
| 3  | people   |
| 4  | privacy  |
| 5  | messages |
+----+----------+

categories_questions
+------------------+-------------+
| id | question_id | category_id |
+------------------+-------------+
| 1  |           1 |           2 |
| 2  |           2 |           5 |
| 3  |           3 |           2 |
| 4  |           4 |           4 |
| 5  |           4 |           2 |
| 6  |           5 |           4 |
+------------------+-------------+

I want to get all of the categories and count the number of questions (question_id) for each category.

Say the first category, billing, will have 1 question, and the second, security, will have 3 questions.

I tried this:

SELECT categories.*, count(categories_questions.id) AS numberOfQuestions
FROM categories
INNER JOIN categories_questions
ON categories.id = categories_questions.category_id
+3
source share
3 answers

You want to do this:

SELECT categories.id, max(categories.slug), count(categories_questions.id) AS numberOfQuestions
FROM categories
LEFT JOIN categories_questions
ON categories.id = categories_questions.category_id
group by categories.id

LEFT JOIN will make sure that the categories without questions fall into the list with count = 0

+6
source

This should do it ... you just need to aggregate something before counting:

SELECT categories.*, COUNT(categories_questions.id) AS numberOfQuestions FROM categories
INNER JOIN categories_questions
ON categories.id = categories_questions.category_id
GROUP BY categories.id
0
source

String p_query = "select c. *, Count (o.id) from CustomerTable c, OrderTable o where c.id = o.customerId GROUP BY c.id";

Hope this helps you. thank

0
source

More articles:


All Articles