how to get count(*) from table which either has column or not?

Question

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?

Answer 1

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);

Answer 2

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.