How to Write Such a Complex Join Query
Hi,
I’m trying to write a complex sql query in MySQL, but I couldn’t manage to write it. I have 3 tables; categories, posts and comments.
Code:
CREATE TABLE `categories` (
`id` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(30) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)ENGINE=MyISAM
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE TABLE `posts` (
`id` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`category_id` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`title` VARCHAR(150) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`content` TINYTEXT NOT NULL,
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)ENGINE=MyISAM
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE TABLE `comments` (
`id` MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
`post_id` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`member_id` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
`comment` TEXT COLLATE utf8_general_ci NOT NULL,
`ip` INTEGER(11) NOT NULL DEFAULT '0',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
I want to join these 3 tables and get a result something like that;
Code:
category.id | category.title | post.id | post.title | post.content | comment_count
----------------------------------------------------------------------------------
1 Category 1 1 Post 1 Post content 5
1 Category 1 2 Post 2 Post content 3
2 Category 2 3 Post 3 Post content 0
3 Category 3 4 Post 4 Post content 1
It’s important for me to get "posts.status = 1 AND comments.status = 1". I want to see the records that posts.status = 1 AND comments.status = 1. If there are no comments or comments were not approved (which is comments.status = 0), "comment_count" row will be shown as 0 (zero).
How can I write such a query?
Thanks.
