title: Need help with SQL query for comments moderation
body: <p>I am developing a comments moderating system as part of my website so i can see a list of comments before they are published so i can either publish them as okay or delete them if inappropiate.</p>
  
  <p>I have haof of my SQL working ok where i can see who the author of the comment is, the date posted, the comment etc and even what page it was on (whether it was a news page, blogs or events page).</p>
  
  <p>What i can't get it to do however is display the title of the page (news title, blog title or event title).</p>
  
  <p>This would really help so i would like it incorporated into my query.</p>
  
  <p>How my query works now is that it select everything in the comments table including the physical page (news, blogs, events). </p>
  
  <p>What i imagine it to do is once it knows what page the comment belongs to, is query that table and get the title.</p>
  
  <p>Has anyone any idea how i do this?  Do i need a nested select once the page has been chosen?</p>
  
  <p>Here is my current query (the $get_id) gets the comment id from the url)</p>
  
  <pre><code>   SELECT c . * , ifnull( cc.commentcount, 0 ) AS ccount
          FROM comments c
          LEFT OUTER
          JOIN (
  
          SELECT page, pageid, count( * ) AS commentcount
          FROM comments
          GROUP BY page
          ) AS cc ON cc.pageid = c.pageid
          WHERE c.commentid = '$get_id'
  </code></pre>
  
  <p>and here is database structure if it helps</p>
  
  <pre><code>CREATE TABLE `comments` (
    `commentid` int(5) NOT NULL auto_increment,
    `page` varchar(20) NOT NULL default '',
    `pageid` int(3) NOT NULL default '0',
    `user` varchar(40) NOT NULL default '',
    `email` varchar(100) NOT NULL default '',
    `website` varchar(100) NOT NULL default '',
    `comment` text NOT NULL,
    `posted` datetime NOT NULL default '0000-00-00 00:00:00',
    `status` enum('0','1') NOT NULL default '0',
    PRIMARY KEY  (`commentid`)
  )
  
  // the tables for news, blogs and events are pretty much the same
  CREATE TABLE `news` (
    `id` int(4) NOT NULL auto_increment,
    `title` varchar(100) NOT NULL default '',
    `content` text NOT NULL,
    `postdate` date NOT NULL default '0000-00-00',
    `photo` varchar(50) NOT NULL default '',
    `alternate` varchar(50) NOT NULL default '',
    `archived` char(1) NOT NULL default 'n',
    `page` varchar(4) NOT NULL default 'news',
    PRIMARY KEY  (`id`)
  )
  </code></pre>
  
  <p>I don't know if this helps but this is how i display the comments once published</p>
  
  <pre><code>SELECT  c.*, 
      b.title,  
      ifnull(cc.commentcount,0) as ccount 
          FROM comments c 
      INNER 
          JOIN blogs b
              ON c.pageid = b.id 
      LEFT OUTER
          JOIN (SELECT page,pageid, 
                       count(*) as commentcount 
                  FROM comments 
                  GROUP BY page) as cc 
  
              on cc.pageid = c.pageid
      WHERE c.pageid='$blogid'
  </code></pre>
  
  <p>EDIT: Current query with proposed changes from Ain</p>
  
  <pre><code>SELECT c. * , ifnull( cc.commentcount, 0 ) AS ccount,
  CASE c.page
  WHEN 'news'
  THEN SELECT title
  FROM news
  WHERE id = c.pageid
  WHEN 'blog'
  THEN SELECT title
  FROM blog
  WHERE id = c.pageid
  ELSE ''
  END FROM comments c
  LEFT OUTER JOIN (
  
  SELECT page, pageid, count( * ) AS commentcount
  FROM comments
  GROUP BY page
  ) AS cc ON cc.pageid = c.pageid
  WHERE c.commentid = '2'
  </code></pre>