SQL query not returning results when expected
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
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?
Are you 100% sure that your WHERE clause actually has a results set?
Also what is the format of table 'users'?
精彩评论