Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    23

    Unanswered: How to Write Complex Join Query

    Hi,

    I’m trying to write a complex sql query, 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
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    23
    r937, that was exactly what I wanted. Thank you so much, you saved my life. Works perfect.

    Thanks...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks, but you made it really easy for me, i wish more people would post their tables and describe their problems with such clarity

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2009
    Posts
    23
    My English is not so good, but I always try to describe the problem as clear as possible.

    Btw, which software do you use to beautify your code? I looked at some your posts and all of your queries are so well-organized; like indents and line breaks.

    Thanks again.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by demods View Post
    Btw, which software do you use to beautify your code? I looked at some your posts and all of your queries are so well-organized; like indents and line breaks.
    i use a simple text editor, which happens to be UltraEdit

    in the past i used to use PFE ("programmers file editor") and before that, windows notepad

    thank you for the very kind words about my coding style

    you have made this old man very happy today

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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
  •