SQL query not returning results when expected

Question

I am having trouble with my queries not returning any results. There are no errors in the query but i expect to see a result and i don't get any

Here is my table structure

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

and here is my query

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')

I think it's becuase i'm using inner joins and maybe i should be using outer joins

Answer 1

Are you 100% sure that your WHERE clause actually has a results set?

Also what is the format of table 'users'?

Answer 2

The final subquery-based condition

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

can be simplified to

b.boardname = 'General Chit Chat'

Some questions:

• Are you sure that topic 'Honeylands Respite' exists?
• Are you sure that board 'General Chit Chat' exists?
• Are you sure that topic 'Honeylands Respite' is on board 'General Chit Chat'?
• Are you sure that there are any messages on topic 'Honeylands Respite'?
• What is the structure (and the purpose) of table misc?
• Should the join to table misc be an outer join?