Professional Web Applications Themes

Forum query without subselect - MySQL

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 / ...

  1. #1

    Default 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@yahoo.de Guest

  2. #2

    Default Re: Forum query without subselect

    de wrote: 

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


    Paul Guest

  3. #3

    Default Re: Forum query without subselect


    Paul Lautman wrote: 
    >
    > You need what I call the "Strawberry Query"
    > See the pattern for it in my response on this thread:
    > http://tinyurl.com/yex7s2[/ref]

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

    Happy New Year!

    strawberry Guest

  4. #4

    Default Re: Forum query without subselect


    strawberry wrote:
     
    > >
    > > You need what I call the "Strawberry Query"
    > > See the pattern for it in my response on this thread:
    > > http://tinyurl.com/yex7s2[/ref]
    >
    > :-) FWIW, i call it the Karwin query!
    >
    > Happy New Year![/ref]
    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 Guest

  5. #5

    Default Re: Forum query without subselect

    Captain Paralytic wrote: 

    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 Guest

  6. #6

    Default Re: Forum query without subselect


    Bill Karwin wrote:
     
    >
    > 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.[/ref]

    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.

    Captain Guest

Similar Threads

  1. HELP with SQL subselect
    By starman7@hotmail.com in forum MySQL
    Replies: 3
    Last Post: December 1st, 02:20 AM
  2. Writing SQL query without subselect
    By Steven Stern in forum PHP Development
    Replies: 15
    Last Post: October 15th, 08:27 AM
  3. subselect argh!
    By Getho in forum Macromedia Freehand
    Replies: 7
    Last Post: April 6th, 07:13 PM
  4. Forum Query
    By ^MisterJingo^ in forum PHP Development
    Replies: 2
    Last Post: July 20th, 08:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139