SQL: joins, max values and group by....

[jantilly]

Hi there,

I'm trying to code a little community system for my page, but I have a problem with one of
the SQL queries.
There are the tables threads,posts and user. The query I have the problem with gets the Parameter
boardid (there is such a col in threads and posts) and I'm trying to get all the threads in that
board. With these threads, I need the topic,time and authorid of the first posting with that threadid
out of the table posts and the oldest one with the same information. The result should be ordered
by the time of the latest post of each thread.

So far so good, sounds like I need a couple of Joins and the min/max stuff. So far I tried this:

 SELECT T.threadid,P.boardid,P.topic,P.userid AS authorid,
	max(P2.postid) AS postid,max(P2.time) AS time_last_reply,
	max(P2.userid) AS authorid_last_reply,B.name AS boardname,
	U.username,max(U2.username) AS author_last_reply,
	(COUNT(P3.postid)) AS replies 
		FROM ".PREFIX."threads AS T 
			LEFT JOIN ".PREFIX."posts AS P using(threadid) 
			LEFT JOIN ".PREFIX."boards AS B using(boardid) 
			LEFT JOIN ".PREFIX."user AS U ON P.userid=U.userid 
			LEFT JOIN ".PREFIX."posts AS P2 ON P2.threadid=T.threadid 
			LEFT JOIN ".PREFIX."posts AS P3 ON P3.threadid=T.threadid,
			LEFT JOIN ".PREFIX."user AS U2 ON U2.userid=P3.userid
				WHERE T.boardid=".$_REQUEST['boardid']." AND P.firstposting='1' 	
					GROUP by P2.threadid 
						ORDER by P3.time desc

You see, to get all the counting done and to get the maximum value of the entry time I'm doubling/tripling some of the tables (don't know if it really makes sense)
Right now I'm getting mostly the correct results except for the data of the lastposting where I'm using that max() thing. The date that is returned is correct but userid/posting id dont fit with it, so there must be a problem with the grouping...

Can you guys help me, getting this query to work, or should I try it a different way, like using three queries and than merging the results afterwards?

thanks a lot!

jan

[steadyguy]

I would recommend reworking this query. Once the table has many records, it will take a very long time to execute.

I would start off writing in words what information you need, and what tables it comes from; for most applications, it is unlikely that you will ever need more than 2 JOINs, and you definitely don't need to JOIN on the same table more than once.

I also noticed there's an unclosed parenthesis and an extra comma in there, too ...

[jantilly]

Ok, so I better forget that query ;-)
I can use 2 queries, etc. to get similar results, but one problem still exists:

My basic problem (when using several queries) is this:

SELECT P.postid,max(P.time) AS time,P.userid,U.username FROM posts AS P JOIN user AS U using(userid) WHERE P.threadid=%ID% GROUP by P.threadid ORDER by P.time

The result doesnt contain the information about the posts in the originally relation as it is in the table.... I get the correct time, when I use max(P.userid) I also get the right userid, but I don't get the right name of that userid, which is stored in the user table....
Anyhow the result doesnt seem to be grouped right...

The easy way is of course storing stuff like "time_last_reply", "author_last_reply" etc. in the table_threads but Im trying to avoid that...

thanks a lot ;-)