SQL 查询未按预期返回结果

Question

我的查询遇到问题，没有返回任何结果。查询中没有错误，但我希望看到结果，但没有得到任何结果

这是我的表结构

CREATE TABLE IF NOT EXISTS `boards` (
  `boardid` int(2) NOT NULL auto_increment,
  `boardname` varchar(255) NOT NULL default '',
  `boarddesc` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`boardid`)
);

CREATE TABLE IF NOT EXISTS `messages` (
  `messageid` int(6) NOT NULL auto_increment,
  `boardid` int(2) NOT NULL default '0',
  `topicid` int(4) NOT NULL default '0',
  `message` text NOT NULL,
  `author` varchar(255) NOT NULL default '',
  `postdate` datetime default NULL,
  PRIMARY KEY  (`messageid`)
);

CREATE TABLE IF NOT EXISTS `topics` (
  `topicid` int(4) NOT NULL auto_increment,
  `boardid` int(2) NOT NULL default '0',
  `topicname` varchar(255) NOT NULL default '',
  `author` varchar(255) NOT NULL default '',
  `counter` int(5) NOT NULL default '0',
  `sticky` char(1) NOT NULL default 'n',
  `locked` char(1) NOT NULL default 'n',
  PRIMARY KEY  (`topicid`)
);

CREATE TABLE IF NOT EXISTS `users` (
  `userid` int(25) NOT NULL auto_increment,
  `first_name` varchar(25) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `username` varchar(25) NOT NULL default '',
  `password` varchar(32) NOT NULL default '',
  `salt` char(3) NOT NULL default '',
  `sex` varchar(6) NOT NULL default '',
  `user_level` enum('0','1','2','3') NOT NULL default '0',
  `signup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
  `activated` enum('0','1') NOT NULL default '0',
  PRIMARY KEY  (`userid`)
)

这是我的查询

SELECT  b.boardid
        , b.boardname
        , t.topicid
        , t.topicname as topic
        , m.author as mauthor
        , m.message as message
        , DATE_FORMAT(m.postdate, '%M %d, %Y, %r') as postdate
        , tm.post_count as posts
        , u.user_level
        , DATE_FORMAT(signup_date, '%b %Y') as joindate
        , ms.avatar
        , ms.signature
    FROM topics t
INNER
    JOIN boards b
        ON t.boardid = b.boardid
INNER 
    JOIN messages m 
        ON t.topicid = m.topicid
INNER 
    JOIN users u
        ON m.author = u.username
INNER 
    JOIN misc ms
        ON ms.userid = u.userid
INNER
    JOIN (SELECT author
               , COUNT(*) as post_count
            FROM messages
          GROUP
            BY author) as tm
        ON tm.author = m.author
    WHERE t.topicname = 'Honeylands Respite' AND b.boardname = (SELECT boardname FROM boards WHERE boardname='General Chit Chat')

我认为这是因为我正在使用内部联接，也许我应该使用外部联接

Answer 1

最终基于子查询的条件

b.boardname = (SELECT boardname FROM boards WHERE boardname='General Chit Chat')

可以简化为

b.boardname = 'General Chit Chat'

一些问题:

• 您确定主题“Honeylands”吗？“喘息”存在吗？
• 你确定吗“常规聊天”板存在吗？
• 是你确定主题“蜂蜜之地”“喘息”在“General Chit”号上聊天'？
• 你确定有吗关于“蜂蜜之地”主题的任何消息喘息'？
• 结构是什么(以及表杂项的目的)？
• 应该与表杂项的连接是外部的加入吗？