[SOLVED] joining 2 working queries to make 1 query

[AdRock]

MySQL version 4.2.1

 

Table structure

 

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


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 '',
  `date` timestamp(14) NOT NULL,
  PRIMARY KEY  (`messageid`)
);

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

 

 

I have ugraded mysql and I can now user nested queries at last

 

I have 2 queries which i have written and they both work on their own but i would like to combine them to make 1 query.

 

What it is supposed to do is have the board name from the board table, a count of all topics in the topics table and count of all messages in the message table relating to that board.  This works fine on it's own.

 

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

 

I have the second query that gets the message author, date from messages table and topic from topics table where the date is MAX so the last post

 

SELECT m.author, m.date, t.topicname
FROM messages m
INNER JOIN topics t ON t.topicid = m.topicid
WHERE date = (
SELECT max( date )
FROM messages ms
WHERE ms.topicid = t.topicid ) 

 

I need to join the 2 queries so i can get all this information in 1 query

[aschk]

The queries don't seem like they're going to be similar in what they display. the first query looks at the boards, but the 2nd query is looking at topics... significantly different unless i'm mistaken.

 

How about you give us a sample of what you suspect the query might look like, and also what you expect the output to look like.

[AdRock]

I have come up with this query

SELECT b.boardid, b.boardname, count( distinct t.topicname ) AS topics, count( m.message ) AS messagem, m.author, m.date, t.topicname
FROM boards b
INNER JOIN topics t ON t.boardid = b.boardid
INNER JOIN messages m ON t.topicid = m.topicid
WHERE date = (
	SELECT max( date )
	FROM messages ms
	WHERE ms.topicid = t.topicid ) GROUP BY boardname ORDER BY boardname ASC[/

 

this is the output

boardname      topics messagem    author    date                         topicname

board 1         2   2         me        2009-04-05 18:25:16   topic 1

 

and this is what i would expect

 

boardname      topics messagem    author    date                         topicname

board 1         2   10         me        2009-04-05 18:25:16   topic 1

 

this would be for each board but i only have 1 board at the minute for testing purposes but with 2 topics with 5 messages in each topic[/code]

[AdRock]

I have nearly got it

 

SELECT b.boardid, b.boardname, count( DISTINCT t.topicname ) AS topics, count( m.message ) AS message, m.author as author, m.date as date, t.topicname as topic
FROM boards b
INNER JOIN topics t ON t.boardid = b.boardid
INNER JOIN messages m ON t.topicid = m.topicid
INNER JOIN (
SELECT topicid, max( date )
FROM messages ms
GROUP BY topicid ) test ON t.topicid = test.topicid
GROUP BY boardname
ORDER BY boardname ASC

 

it does what I want but doesn't get the right author name for the last post

[fenway]

That's because you're requested non-aggregated fields in the select list AND using group by.