Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a post table as below:

CREATE TABLE `post` (
  `p_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` smallint(5) unsigned NOT NULL,
  `cat_id` tinyint(3) unsigned NOT NULL,
  `view_count` smallint(5) unsigned NOT NULL DEFAULT '0',
  `status` tinyint(3) unsigned NOT NULL,
  `abstract` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `content` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `title` varchar(50) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL DEFAULT '',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `meta_description` tinytext CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `picture` varchar(100) NOT NULL,
  PRIMARY KEY (`p_id`),
  KEY `user_id` (`user_id`),
  KEY `date` (`date`),
  KEY `pic_id` (`picture`),
  KEY `cat_id` (`cat_id`),
  CONSTRAINT `post_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `karbar` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `post_ibfk_3` FOREIGN KEY (`cat_id`) REFERENCES `category` (`cat_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

And I have a comment table as below:

CREATE TABLE `comment` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `p_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `name` varchar(50) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL DEFAULT '',
  `site` varchar(200) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `content` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `p_id` (`p_id`),
  CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `post` (`p_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

I want to get one post(e.g p_id=8) and all of its comments. how to do that? How to get count(*) from comment table while your getting your post record?

share|improve this question
1  
Do you want to get all of the comments in a single SQL statement, or a given post plus a count of the comments in a single row? Your question is confusing. –  FreshPhilOfSO Apr 26 '12 at 8:28
    
One post with count of the comments. –  john.locke Apr 26 '12 at 8:37
    
A SELECT * FROM post AS p INNER JOIN (SELECT p_id, COUNT(*) AS cnt FROM comment GROUP BY p_id) AS c ON c.p_id = p.p_id should do –  Lieven Keersmaekers Apr 26 '12 at 8:54
    
I tried this but this is not the right solution for me, when there is no comment I still want to get the result set from post and 0 from cnt. your query gives nothing if we don't have a comment for the post. –  john.locke Apr 26 '12 at 11:35
add comment

2 Answers

up vote 2 down vote accepted

This will get all posts and their comment counts

SELECT p.*,IFNULL(c.comcount,0) comment_count
FROM post p LEFT JOIN
(SELECT p_id,COUNT(1) comcount FROM comment GROUP BY p_id) c
USING (p_id);

This will get post for p_id=8 and its comment count

SELECT p.*,IFNULL(c.comcount,0) comment_count
FROM (SELECT * FROM post WHERE p_id=8) p LEFT JOIN
(SELECT p_id,COUNT(1) comcount FROM comment WHERE p_id=8 GROUP BY p_id) c
USING (p_id);
share|improve this answer
    
I wasn't familiar with left join, so that's why I couldn't solve the issue! thanks –  john.locke Apr 27 '12 at 1:43
add comment
SELECT p.*, (SELECT count(*) FROM Comment c WHERE c.p_ID = p.p_ID) CommentCount
FROM Post p WHERE p.p_ID = 8;

In this case a function isn't necessary because the nested query will always return a number even if the number is zero. If you want to see the output for all accounts simply remove the WHERE clause.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.