Results 1 to 2 of 2

Thread: How to Write Such a Complex Join Query

  1. #1
    Join Date
    Dec 2009
    Posts
    3

    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.

  2. #2
    Join Date
    Dec 2009
    Posts
    3
    Got my answer from another board. Thanks to r937 from dbforums.com

    Code:
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •