Mysql – How to match all words of one column against words in another column

full-text-searchMySQLpattern matching

I have two tables.

  • Table A:

    Company_name
-------------------------------
Tata consultancy 
Infosys tech
Atm service
Air force firm

  • Table B: 

    Company1_name                    |  Id
--------------------------------   -----
Atm and service.                 |   1
Honey tech.                      |   2
Tata & consultancy.              |   3
Graft soft.                      |   4
Atm, service.                    |   5

I want this result:

Id
----
1
3
5

Description:

For each Company_name in table A, find a row in table B whose company1_name value has all the words from table A's value, and return the matching row's Id.

How to design a query for this in MySQL?

Best Answer

For a quick and dirty solution - not 100% accurate due to some full-text search specifics but will get most of the matches - add a FULLTEXT key to A and then use a join with be with MATCH AGAINST as the join condition. Since MATCH AGAINST cannot use a a non-constant argument, you will have to simulate the join with a cursor in a stored procedure. Below is a fully-functional tested example:

create table a (id int not null primary key, 
company_name text, fulltext key(company_name)) engine=myisam;
create table b (id int not null primary key, company_name1 text);
insert into a values(1,'dog kitten'),(2,'spoon fork'),
  (3,'fish crab'),(4,'dog mouse'),(5,'noise mouse'),
(6,'kitten dog'),(7,'noise one'),(8,'noise two'),
  (9,'noise three'),(10,'noise four'),(11,'noise five');
insert into b values(1,'dog mouse kitten'),
(2,'spoon knife fork'),(3,'fish sea crab');

drop procedure if exists ft_match;
delimiter //
create procedure ft_match()
language sql
deterministic
sql security definer
begin
    declare v_id int;
    declare v_company_name1 text;
    declare v_finished int;
  declare c cursor for select * from b;
    declare continue handler for not found set v_finished=1;
  delete from results;
  open c;
c_loop: loop
    fetch c into v_id,v_company_name1;
    if v_finished then
        leave c_loop;
    end if;
    insert into results select v_id,v_company_name1,a.id,a.company_name
     from a where match (a.company_name) against (v_company_name1 in boolean mode);
    end loop c_loop;
    close c;
    select * from results;
end//
delimiter ;

create table results (a_id int not null, a_company_name text, 
b_id int not null, b_company_name text);
call ft_match();

More info on full text keys in the MySQL manual at https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

This is suitable for relatively small tables and in circumstances where high latency is acceptable. For better performance on large datasets and perfect accuracy, you will need to implement some form of external full text indexing.

Related Question