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