MySQL query for selecting and counting some fields from two tables

Question

There is MySQL table post:

mysql> desc post;
+---------------+----------------+------+-----+---------+----------------+
| Field         | Type           | Null | Key | Default | Extra          |
+---------------+----------------+------+-----+---------+----------------+
| post_id       | int(11)        | NO   | PRI | NULL    | auto_increment |
| post_content  | varchar(50000) | NO   |     | NULL    |                |
| post_date     | datetime       | NO   |     | NULL    |                |
| post_summary  | varchar(1000)  | YES  |     | NULL    |                |
| post_title    | varchar(300)   | NO   |     | NULL    |                |
| post_visitors | int(11)        | NO   |     | NULL    |                |
| user_id       | int(11)        | NO   | MUL | NULL    |                |
| category_id   | int(11)        | NO   | MUL | NULL    |                |
+---------------+----------------+------+-----+---------+----------------+

Then, there is table comment:

mysql> desc comment;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| comment_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| comment_content | varchar(600) | NO   |     | NULL    |                |
| comment_date    | datetime     | NO   |     | NULL    |                |
| comment_title   | varchar(300) | NO   |     | NULL    |                |
| user_id         | int(11)      | NO   | MUL | NULL    |                |
| post_id         | int(11)      | NO   | MUL | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

You can see there is foreign key post_id in the table comment that points to table post. I need to get next resultset: post_id, post_title, post_date, post_summary, number-of-post-comments.

I tried this, but I'm not getting proper result:

SELECT 
  p.post_id,
  p.post_date,
  p.post_summary,
  p.post_title,
  COUNT(c.post_id) 
FROM
  post p 
  LEFT JOIN COMMENT c 
    ON p.post_id = c.post_id

(I don't work with sql often, this should be easy for those familiar with sql)

M Khalid Junaid · Accepted Answer · 2014-01-04 11:14:37Z

3

Try this

  SELECT 
  p.post_id,
  p.post_date,
  p.post_summary,
  p.post_title,
  COUNT(c.post_id) AS number_of_post_comments 
FROM
  post p 
  LEFT JOIN COMMENT c 
    ON p.post_id = c.post_id 
GROUP BY p.post_id

edited Jan 4, 2014 at 11:14

answered Jan 4, 2014 at 10:59

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vladimir Over a year ago

@M Khalid Junaid Your first answer gave proper result (before you have edited it). This one, for posts that have no comments returns like they have one comment.

M Khalid Junaid Over a year ago

@Wlad reverted, i have used case for if there is null against post id in comments table then it will be counted as 0

Collectives™ on Stack Overflow

MySQL query for selecting and counting some fields from two tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related