Hi all,
Stumped again! I'm working on a posting forum type thing (normal stuff:
user->picturethreads->picturemessages)
And what I wanted to do was a one query wonder to display list of threads, thread creator, num of messages under thread, last message added to thread date and creator of last message! (phew)
I got it working in Microsoft Access - but didn't realise at the time MySQL doesn't support the Last() statement...
This is what it looks like in Access SQL:
Code:
SELECT picturethreads.thread, users.username, Count(picturemessages.messageid) AS CountOfmessageid, Last(users_1.username) AS LastOfusername, Max(picturemessages.datestamp) AS MaxOfdatestamp, Max(picturemessages.timestamp) AS MaxOftimestamp
FROM ((picturethreads INNER JOIN picturemessages ON picturethreads.threadid = picturemessages.threadid) INNER JOIN users ON picturethreads.userid = users.userid) INNER JOIN users AS users_1 ON picturemessages.userid = users_1.userid
WHERE (((picturethreads.pictureid)=1))
GROUP BY picturethreads.thread, users.username, picturethreads.threadid, picturethreads.datestamp, picturethreads.timestamp
ORDER BY picturethreads.datestamp DESC , picturethreads.timestamp DESC
As you can see I also bring the users table in twice - one on each end of the query flow. The max on the dates and times ensure that I get the most recent message, while the last() function on the username gives me the last user that posted a message.
I'm gonna give up and do two MySQL queries instead - but just wondered if anyone could recommend a way of doing 1 query to get the above???
J
Codewalkers Forums Sponsor:
November 22nd, 2003, 10:01 PM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
