On Mar 17, 12:16 pm, "strawberry" <zac.ca...@gmail.com> wrote:
> joerivi...@gmail.com wrote:
> > On Mar 16, 10:17 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > wrote:
> > > joerivi...@gmail.com wrote:
> > > > 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
>
> > > First off you should be using joins not sub selects.
>
> > > Please explain what the difference is between a thread message and a post
> > > message?
>
> > 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)
>
> 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.


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