Keyword searching accross multiple tables

[igw202]

lo all

been trying to get this to work correctly for some time now - basically i want to search for multiple keywords accross two tables. The query i have already works to a certain extent but doesnt return the exact results i'd like.

I got two tables, both are for a bulletinboard, one contains the threads (bulletinboard) and the other the replies for each thread (bulletinreplys). They're linked via a column called 'threadid' in the reply table.

Here's the current query:

SELECT bulletinboard.*,bulletinreplys.reply FROM bulletinboard LEFT OUTER JOIN bulletinreplys ON bulletinboard.id=threadid WHERE (subject LIKE '%hello%' OR message LIKE '%hello%' OR reply LIKE '%hello%') AND (subject LIKE '%forumum%' OR message LIKE '%forumum%' OR reply LIKE '%forumum%') GROUP BY bulletinboard.id ORDER BY subject ASC

Columns used above:

message - the thread's body
subject - the thread's subject
reply - a users reply

This works - BUT it wont return threads that contain one keyword in one reply and another in a different reply

I'd like to search for ALL keywords no matter which reply they appear in.

Anyone help?

thanks

 

[igw202]

no-one have any ideas on this?

 

[bastienk]

have you tried

SELECT bulletinboard.*,bulletinreplys.reply FROM bulletinboard LEFT OUTER JOIN bulletinreplys ON bulletinboard.id=threadid WHERE (subject LIKE '%hello%' OR message LIKE '%hello%' OR reply LIKE '%hello%') OR (subject LIKE '%forumum%' OR message LIKE '%forumum%' OR reply LIKE '%forumum%') GROUP BY bulletinboard.id ORDER BY subject ASC

making the main sections an OR


Bastien

Cat, the other other white meat

 

[igw202]

yea done that and it works, but the problem is it searches for any keyword and not all of them.. so its kinda useless