I am not that strong with SQL and and maybe I shouldnt even be using the IN statement for this..
Here is what I am trying to accomplish: I have a home made message board on my website. I am working on a search function for it. I hold all posts / replies in the same database table. I have a field called 'id' which entries in the same forum topic share. So for instance this could be an example table:
Fields: id | title | message | show_date | time | userid_who_posted | main_post
Entries: 1 | topic 1 | message for topic 1 | Oct 10, 2005 | (timestamp) | adamthenewbie | true
2 | topic adam 2 | message for topic 2 | Oct 12, 2005 | (timestamp) | matthew | true
1 | topic 1 | message entered adam | Oct 20, 2005 | (timestamp) | dave | false
So you notice some entries share the same id because they belong to the same topic.
Here is my SQL query so far
SELECT m.id, m.title, m.show_date, m.userid_who_posted FROM messageboard m WHERE m.id IN (SELECT DISTINCT(m2.id) FROM messageboard m2 WHERE m2.message LIKE '%Adam%' OR m2.title LIKE '%Adam%') ORDER BY m.time DESC LIMIT 0, 25
So what I want to return with the SQL would be
id = 1, title = topic 1, show_date = Oct 10, 2005, userid_who_posted = adamthenewbie
id = 2, title = topic adam 2, show_date = Oct 12, 2005, userid_who_posted = matthew
and even so the 3rd entry contains the word adam in the message I dont want it to be returned but I WANT TO RETURN the parent of that post
Anyway not sure what the best way to do this is. I am getting this error
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT m2.id FROM messageboard m2 WHERE m2.message LIKE '%Adam
