CODEWITHSUNDEEP
mysql
Vladimir
Vladimir

Reputation: 1694

MySQL query for selecting and counting some fields from two tables

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)

Upvotes: 2

Views: 75

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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

Upvotes: 3

Related Questions