Page 1 of 1

joins and max()

Posted: Tue Sep 30, 2003 1:49 pm
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:



code:--------------------------------------------------------------------------------
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 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

Posted: Tue Sep 30, 2003 4:12 pm
by Stoker
You cant include anything else than PK's and the max/min/avg columnt when doing such (max,min or avg)..

Fin youd max first, then do a query for that ID with all your joins..