joeriviera@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.