[SOLVED] Nested queries not working

[AdRock]

MySQL version 4.0.23

 

I am making a database for a small message board similar to this forum.  I have got the board index to display the subject, author and post count but what I can't get it to do is get the last post in the messages table and display the message author, the date it was posted and the topic.

 

This is my query that works for selecting the subject, author and post count

SELECT boards.boardid, boardname, count( distinct topicname ) AS topics, count( message ) AS message
FROM boards
INNER JOIN topics ON boards.boardid = topics.boardid INNER JOIN messages ON messages.topicid = topics.topicid
GROUP BY boardname ORDER BY boardname ASC

 

Does my version of MySQL not support nested queries?

 

I have tried 2 different queries which i have been told is standard SQL

 

SELECT t.topicname, t.author, 
      (select count(message) from messages m where m.topicid = t.topicid) AS messagecount, 
       lm.author, lm.date
  FROM topics t
  INNER JOIN messages lm
    ON lm.topicid = t.topicid AND lm.date = (SELECT max(m2.date) from messages m2)
  INNER JOIN boards b
    ON b.boardid = t.boardid
  WHERE b.boardid = 1
  GROUP BY t.topicname

 

the error from this query is

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count( message ) from messages m where m . topicid = t .

 

SELECT t.topicname, t.author, mc.messagecount, lm.author, lm.date
  FROM topics t
  JOIN (select m.topicid, count(*) as messagecount from messages m group by m.topicid) as mc 
    ON mc.topicid = t.topicid
  JOIN messages lm
    ON lm.topicid = t.topicid AND lm.date = (SELECT max(m2.date) from messages m2)
  WHERE t.boardid = 1
  GROUP BY t.topicname

 

the error from this query is

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select m . topicid , count( * ) as messagecount from messages m

 

Table structure

 

-- Table structure for table `boards`
-- 

CREATE TABLE `boards` (
  `boardid` int(2) NOT NULL auto_increment,
  `boardname` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`boardid`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `messages`
-- 

CREATE TABLE `messages` (
  `messageid` int(6) NOT NULL auto_increment,
  `topicid` int(4) NOT NULL default '0',
  `message` text NOT NULL,
  `author` varchar(255) NOT NULL default '',
  `post_date` datetime default NULL,
  PRIMARY KEY  (`messageid`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `topics`
-- 

CREATE TABLE `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 '',
  PRIMARY KEY  (`topicid`)
) TYPE=MyISAM;

[PFMaBiSmAd]

Yes, subqueries were added in 4.1. Why on earth are you using such an old version?

[AdRock]

I tried running the query on a new version of MySQL and it worked,

 

I decided to upgrade my version of MySQL and have tried 4.2.1 and 5.1 but i get problems with phpmyadmin

 

MySQL is refusing the connection to phpmyadmin

 

#1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

 

Is there a way I can resolve this issue?

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.1/en/old-client.html

[fenway]

Yes, subqueries were added in 4.1. Why on earth are you using such an old version?

Don't laugh... I have a client still running 3.23 -- works just fine.

[corbin]

But that doesn't mean it couldn't work better ;p.