forum sql help needed

tomallen35 · Freshman Joined: Feb 04, 2005 Posts: 1

can anyone help me with a problem i've been having with my sql select statement i'm trying to write to view all threads on a given topic?

The table structure i'm using is as follows:

CREATE TABLE member
(
mem_id integer NOT NULL,
em_add varchar(50) NOT NULL,
password varchar(20) NOT NULL,
name varchar(15) NOT NULL,
avatar varchar(150) NOT NULL,
points integer,
lastlogin date NOT NULL,
joined date NOT NULL,
PRIMARY KEY (mem_id)
);

CREATE TABLE topic
(
topic_id integer NOT NULL,
title varchar(50) NOT NULL,
description varchar(300) NOT NULL,
PRIMARY KEY (topic_id)
);

CREATE TABLE thread
(
thread_id integer NOT NULL,
topic_id integer NOT NULL,
title varchar(50) NOT NULL,
c_em_add varchar(50) NOT NULL,
created date NOT NULL,
sticky char(1) NOT NULL,
PRIMARY KEY (thread_id)
);

CREATE TABLE message
(
msg_id integer NOT NULL,
thread_id integer NOT NULL,
title varchar(50),
body varchar(1000) NOT NULL,
c_em_add varchar(50) NOT NULL,
created date NOT NULL,
PRIMARY KEY (msg_id)
);

Amongst others....

The code i tried was:

SELECT DISTINCT thread.thread_id,thread.title,member.name,
member.avatar,member.mem_id,thread.created,thread.sticky,MAX(message.created) AS lastPost,COUNT(message.msg_id)
FROM topic,thread,message,member
WHERE message.thread_id = thread.thread_id
AND thread.topic_id = request.getParameter("topic") AND thread.c_em_add = member.em_add
GROUP BY thread.thread_id,thread.title,thread.created,
thread.sticky,topic.title,member.name,member.avatar,member.mem_id
ORDER BY thread.sticky DESC, lastPost DESC;

All this outputs is one thread so when a new thread is created, it is not showing up.

Any ideas?