joins and max()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jantilly
Forum Newbie
Posts: 1
Joined: Tue Sep 30, 2003 1:49 pm
Contact:

joins and max()

Post 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
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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..
Post Reply