asked on

MYSQL KEYWORD MATCH query on two columns from different tables?

Hi

Is it possible to perform a MATCH keyword search on a column from one table with another column from a second table (these two tables are joined on an ID).

Table A
PK - an_ID
Title

Table B
FK - an_ID
Content

Both Title and Content are TEXT fields.
Both have had a FULLTEXT search added to their respective table.

Is there one query which will search the 'Title' and/or the 'Content' for a Match against a keyword search?

I have tried:

SELECT *, MATCH( title, content ) AGAINST('keyword%') AS score FROM Table_A t1 INNER JOIN Table_B t2 ON t1.an_ID = t2.an_ID WHERE MATCH( title, content ) AGAINST('keyword%')

I get incorrect arguments to MATCH :(

Any ideas?

Hope that is clear?

Thanks

effx

Have you tried:

SELECT t1.*, MATCH( t1.title, t1.content ) AGAINST('keyword%') AS score FROM Table_A t1 INNER JOIN Table_B t2 ON t1.an_ID = t2.an_ID WHERE MATCH( t2.title, t2.content ) AGAINST('keyword%')

bigMlittleC

ASKER

I have tried that. Still the same error: incorrect match.

(expect I tried it as t1.title, t2.content) as title is in table A (t1) and content is in table B (t2)

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bigMlittleC

ASKER

I have a feeling you could be right.

bigMlittleC

ASKER

No other suggestion so will close as I think johanntagle is right

Ray Paseur

I think you're getting good advice here, but you might also consider something with nested queries like SELECT ... WHERE IN(SELECT...) -- my guess is that you would want to make the IN query be against the title if it is the smaller of the two columns.