Hi there,

I have the following statement

SELECT * FROM messages,threads where messages.userid = 3 order by msgid desc

where there is a one to many relationship regarding threads to messages. The query is supposed to bring back the message details (such as msg_message ) and the thread title that the message relates to. Just using that query brings back repeats of the same rows so i'm guessing a group by clause is required. However the following:

SELECT * FROM messages,threads where messages.userid = 3 group by msgid order by msgid desc

brings back different messages but they all have the same thread_title, regardless of the thread they were posted in. My structure is as follows:

CREATE TABLE threads (
threadid bigint(20) NOT NULL auto_increment,
thread_title varchar(255) NOT NULL default '',
thread_date datetime NOT NULL default '0000-00-00 00:00:00',
userid bigint(20) NOT NULL default '0',
subcatid int(11) NOT NULL default '0',
PRIMARY KEY (threadid)
) TYPE=MyISAM;

CREATE TABLE messages (
msgid bigint(20) NOT NULL auto_increment,
userid int(11) NOT NULL default '0',
msg_message text NOT NULL,
threadid bigint(20) NOT NULL default '0',
subcatid int(11) NOT NULL default '0',
msg_date date NOT NULL default '0000-00-00',
PRIMARY KEY (msgid)
) TYPE=MyISAM;

The SQL needs to be compatible with MySQL 3.x

Any help greatly appreciated!