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

Question

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?

Answer 1

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.

Answer 2

with some MySQL FullText restrictions it possible.

1. You need change settings for FullText Index in my.cnf/my.ini file

innodb_ft_min_token-size=2
ft_min_word_len=2
ft_stopword_file = ""

2. Create FullText index on Table - company_name1, column - Company1_name
3. Add identity column for table company_name, in our case - also named id

Unfortunately, MATCH() AGAINST() not understand SELECT with WHERE condition inside AGAINST() - without WHERE it work, with WHERE - not.

It make impossible using direct JOIN command from 2 tables in single and clean query.

For resolve this we can use stored procedure, which open cursor from table Company_name and populate table Company_text with result

CREATE TABLE `company_text` (
  `id` int(11) DEFAULT NULL,
  `ft_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

code of procedure:

CREATE DEFINER=`root`@`%` PROCEDURE `test_db`.`udp_ft_search_2`()
    READS SQL DATA
BEGIN

DECLARE v_text VARCHAR(20) DEFAULT '';
DECLARE v_id INT;
DECLARE done2 INT DEFAULT FALSE;

DECLARE cur2 CURSOR FOR SELECT id, Tata_consultancy FROM company_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;

TRUNCATE TABLE company_text;

OPEN cur2; 

    read_loop2: LOOP


-- fetch Master ID into variable
    FETCH cur2 INTO v_id, v_text;

    IF done2 THEN
          LEAVE read_loop2;
    END IF;

-- LOOP Body
        IF EXISTS (SELECT Id FROM test_db.company1_name T2 WHERE MATCH(T2.Company1_name) 
                AGAINST(CONCAT('+', replace(v_text, ' ', ' +')) IN BOOLEAN MODE)) 
        THEN
                INSERT INTO test_db.company_text(id, ft_id)
                SELECT v_id, Id FROM test_db.company1_name T2 WHERE MATCH(T2.Company1_name) AGAINST(CONCAT('+', replace(v_text, ' ', ' +')) IN BOOLEAN MODE);
        ELSE
                INSERT INTO test_db.company_text(id, ft_id) VALUES(v_id, NULL);
        END IF;

    END LOOP read_loop2;
    close cur2;

SELECT * FROM test_db.company_text;

END

result will be like this:

ID, FT_ID
-----------------------
1   (null)
2   1
2   5
3   (null)
4   3