• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

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
0
bigMlittleC
Asked:
bigMlittleC
1 Solution
 
effxCommented:
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%')
0
 
bigMlittleCAuthor Commented:
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)
0
 
johanntagleCommented:
As far as I know you cannot span multiple full-text indexed columns in different tables for MATCH.  You need to specify the columns exactly as they were defined when you created the index.  You will need to restructure your database to have the two columns together in one table, then create a one full-text index that has the two columns i.e. alter tablename add fulltext index (title, content);
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
bigMlittleCAuthor Commented:
I have a feeling you could be right.
0
 
bigMlittleCAuthor Commented:
No other suggestion so will close as I think johanntagle is right
0
 
Ray PaseurCommented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now