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)