MYSQL KEYWORD MATCH query on two columns from different tables?

Posted on 2011-10-26
Last Modified: 2012-08-13

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

Table B
FK -  an_ID

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?

Question by:bigMlittleC
    LVL 10

    Expert Comment

    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%')

    Author Comment

    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)
    LVL 24

    Accepted Solution

    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);

    Author Comment

    I have a feeling you could be right.

    Author Comment

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

    Expert Comment

    by: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.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now