# Miscellaneous > Structured Query Language (SQL) >  How to Write Such a Complex Join Query

## demods

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*.



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



```
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.

----------


## demods

Got my answer from another board. Thanks to r937 from dbforums.com



```
SELECT categories.id
     , categories.title
     , posts.id
     , posts.title
     , posts.content
     , COALESCE(c.comment_count,0) AS comment_count
  FROM categories
INNER
  JOIN posts
    ON posts.category_id = categories.id
   AND posts.status = 1
LEFT OUTER
  JOIN ( SELECT post_id
              , COUNT(*) AS comment_count
           FROM comments
          WHERE status = 1
         GROUP
             BY post_id ) AS c
    ON c.post_id = posts.id
```

----------

