Forum query without subselect

Hey guys!
I'm just coding a little forum application and have some trouble with
my "thread-overview" page.
I want to list a table with the following information:

Thread_title | username_of_last_post | datetime_of_last_post

My schema is as follows:

thread (thread_id, thread_title, ...)
post (post_id, thread_id, user_id, date_created, ...)
user (user_id, name, ...)

THE PROBLEM:
As my provider is still using mysql4.0x without subselects, i want to
do this in one query BUT I HAVE NO CLUE HOW TO DO IT!!! In general, it
looks like a simple JOIN operation, but how do I get mysql to show only
the username / datetime of THE LAST post of EACH thread???

Any help is highly appreciated
Thanks a lot!!

Philipp

pillepop2003 (AT) yahoo (DOT) de wrote:

Hey guys!
I'm just coding a little forum application and have some trouble with
my "thread-overview" page.
I want to list a table with the following information:

Thread_title | username_of_last_post | datetime_of_last_post

My schema is as follows:

thread (thread_id, thread_title, ...)
post (post_id, thread_id, user_id, date_created, ...)
user (user_id, name, ...)

THE PROBLEM:
As my provider is still using mysql4.0x without subselects, i want to
do this in one query BUT I HAVE NO CLUE HOW TO DO IT!!! In general, it
looks like a simple JOIN operation, but how do I get mysql to show
only the username / datetime of THE LAST post of EACH thread???

Any help is highly appreciated
Thanks a lot!!

Philipp

You need what I call the "Strawberry Query"
See the pattern for it in my response on this thread:
http://tinyurl.com/yex7s2

Paul Lautman wrote:

pillepop2003 (AT) yahoo (DOT) de wrote:
Hey guys!
I'm just coding a little forum application and have some trouble with
my "thread-overview" page.
I want to list a table with the following information:

Thread_title | username_of_last_post | datetime_of_last_post

My schema is as follows:

thread (thread_id, thread_title, ...)
post (post_id, thread_id, user_id, date_created, ...)
user (user_id, name, ...)

THE PROBLEM:
As my provider is still using mysql4.0x without subselects, i want to
do this in one query BUT I HAVE NO CLUE HOW TO DO IT!!! In general, it
looks like a simple JOIN operation, but how do I get mysql to show
only the username / datetime of THE LAST post of EACH thread???

Any help is highly appreciated
Thanks a lot!!

Philipp

You need what I call the "Strawberry Query"
See the pattern for it in my response on this thread:
http://tinyurl.com/yex7s2

:-) FWIW, i call it the Karwin query!

Happy New Year!

strawberry wrote:

Paul Lautman wrote:
pillepop2003 (AT) yahoo (DOT) de wrote:
Hey guys!
I'm just coding a little forum application and have some trouble with
my "thread-overview" page.
I want to list a table with the following information:

Thread_title | username_of_last_post | datetime_of_last_post

My schema is as follows:

thread (thread_id, thread_title, ...)
post (post_id, thread_id, user_id, date_created, ...)
user (user_id, name, ...)

THE PROBLEM:
As my provider is still using mysql4.0x without subselects, i want to
do this in one query BUT I HAVE NO CLUE HOW TO DO IT!!! In general, it
looks like a simple JOIN operation, but how do I get mysql to show
only the username / datetime of THE LAST post of EACH thread???

Any help is highly appreciated
Thanks a lot!!

Philipp

You need what I call the "Strawberry Query"
See the pattern for it in my response on this thread:
http://tinyurl.com/yex7s2

:-) FWIW, i call it the Karwin query!

Happy New Year!
Happy New Year to you too.

I've never come across a post from Bill where he has used this method,
but I have seen a couple that you did. Maybe I should call it the
Karwin->Strwaberry query?

Captain Paralytic wrote:

I've never come across a post from Bill where he has used this method,
but I have seen a couple that you did. Maybe I should call it the
Karwin->Strwaberry query?

I call it an outer join. ;-)

I've suggested this solution to folks a number of times on newsgroups
and forums. I also covered this and other techniques in a presentation
on outer joins at OSCON last July.

http://www.karwin.com/wiki/index.php/SQL_Outer_Joins

Regards,
Bill K.

Bill Karwin wrote:

Captain Paralytic wrote:
I've never come across a post from Bill where he has used this method,
but I have seen a couple that you did. Maybe I should call it the
Karwin->Strwaberry query?

I call it an outer join. ;-)

I've suggested this solution to folks a number of times on newsgroups
and forums. I also covered this and other techniques in a presentation
on outer joins at OSCON last July.

http://www.karwin.com/wiki/index.php/SQL_Outer_Joins

Regards,
Bill K.

Whilst I agree it is an outer join, it is the specifics of the join and
where criteria that enables it to discover the first or last, largest
or smallest, ... of groups of data, that sets it as a useful pattern to
solve a specific problem. Hence the need for a name to call it.