Professional Web Applications Themes

complicated mysql query - please help :) - MySQL

Here is the description of my problem: I try to build a search engine for the forum This is the database structure of the forum: Two tables: threads, posts threads: id, subject, message posts: id, thread_id, message This query will print out results - list of id of main threads (SELECT id AS my_id FROM threads WHERE Match(subject,message) Against('keyword')) UNION (SELECT thread_id AS my_id FROM posts WHERE Match(message) Against('keyword') LIMIT 10 OFFSET 0) now i would like to use results from this query to get subject and message for every thread so I was thinking about using IN statement like: ...

  1. #1

    Default complicated mysql query - please help :)

    Here is the description of my problem:


    I try to build a search engine for the forum

    This is the database structure of the forum:

    Two tables: threads, posts

    threads: id, subject, message

    posts: id, thread_id, message


    This query will print out results - list of id of main threads

    (SELECT id AS my_id FROM threads WHERE Match(subject,message)
    Against('keyword'))
    UNION (SELECT thread_id AS my_id FROM posts WHERE Match(message)
    Against('keyword')
    LIMIT 10 OFFSET 0)

    now i would like to use results from this query to get subject and
    message for every thread

    so I was thinking about using IN statement like:

    SELECT * FROM threads WHERE id
    IN
    (SELECT id AS my_id FROM threads WHERE Match(subject,message)
    Against('keyword'))
    UNION
    (SELECT thread_id AS my_id FROM posts WHERE Match(message)
    Against('keyword')
    LIMIT 10 OFFSET 0)


    but this doesnt work :(

    anyone can help me with this?

    thanks,
    Bart

    joeriviera@gmail.com Guest

  2. #2

    Default Re: complicated mysql query - please help :)

    com wrote: 

    First off you should be using joins not sub selects.

    Please explain what the difference is between a thread message and a post
    message?


    Paul Guest

  3. #3

    Default Re: complicated mysql query - please help :)

    On Mar 16, 10:17 pm, "Paul Lautman" <com>
    wrote: 













    >
    > First off you should be using joins not sub selects.
    >
    > Please explain what the difference is between a thread message and a post
    > message?[/ref]


    thread message is the first post in the thread and all other posts in
    the same thread are connected to this post


    joeriviera@gmail.com Guest

  4. #4

    Default Re: complicated mysql query - please help :)

    On Mar 16, 10:17 pm, "Paul Lautman" <com>
    wrote: 













    >
    > First off you should be using joins not sub selects.
    >
    > Please explain what the difference is between a thread message and a post
    > message?[/ref]


    Hi ,

    thread message is the first post in the thread and all posts in the
    same thread are connected to this post ( so threads.id =
    posts.thread_id)

    joeriviera@gmail.com Guest

  5. #5

    Default Look, I just changed the subject!

    com wrote: 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > >
    > > First off you should be using joins not sub selects.
    > >
    > > Please explain what the difference is between a thread message and a post
    > > message?[/ref]
    >
    >
    > Hi ,
    >
    > thread message is the first post in the thread and all posts in the
    > same thread are connected to this post ( so threads.id =
    > posts.thread_id)[/ref]

    I think there's an implicit suggestion in Paul's post that you might
    want to consider dispensing with the threads table - and instead have
    a structure like this:

    post(post_id*,parent_id,subject,message)

    in this scenario a thread is simply a post whose parent_id is NULL. It
    also means that responders can change the Subject as the thread
    evolves - just like I just did.

    strawberry Guest

  6. #6

    Default Re: Look, I just changed the subject!

    On Mar 17, 12:16 pm, "strawberry" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > I think there's an implicit suggestion in Paul's post that you might
    > want to consider dispensing with the threads table - and instead have
    > a structure like this:
    >
    > post(post_id*,parent_id,subject,message)
    >
    > in this scenario a thread is simply a post whose parent_id is NULL. It
    > also means that responders can change the Subject as the thread
    > evolves - just like I just did.[/ref]


    ok thanks for you suggestion ;)
    but I still need to keep some extra information about thread, like how
    many thread has been displayed, how many posts in thread and when last
    post has been added... thats why i needed thread table

    joeriviera@gmail.com Guest

Similar Threads

  1. Complicated SQL query
    By pblecha in forum Coldfusion Database Access
    Replies: 6
    Last Post: April 4th, 05:56 PM
  2. complicated query - please help
    By Ralph in forum MySQL
    Replies: 7
    Last Post: October 8th, 09:58 PM
  3. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  4. Replies: 1
    Last Post: June 26th, 04:09 PM
  5. Requesting help with complicated query
    By Fox in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 7th, 10:46 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