Use results of a mySQL SELECT as input of a MATCH AGAINST natural language query

Question

I need to make a research by natural language on a mySQL table field taking as input the values of another table field. I tried something similar but, as I suspected, it was not correct:

    SELECT id, name, MATCH(name), 
    AGAINST 
      (
        SELECT name
        FROM table2
      ) AS score
    FROM table1
    WHERE MATCH(name), 
    AGAINST 
      (
        SELECT name
        FROM table2
      )

Any idea?

UPDATE 1

I followed the kind example here below but i got the error "#1064 - SQL query syntax error near 'tb2". I cannot see this syntax error. Here the code I am testing:

SELECT name, MATCH(name) AGAINST 
(

    (SELECT name
    FROM 
    (
        SELECT name 
            FROM active_ingredients

        UNION ALL
        
        SELECT active_ingredients.name as name
        FROM active_ingredients
        INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name

    ) tbl
    GROUP BY name
    HAVING count(*) = 1
    ORDER BY name) tb2 

) AS score
FROM 
(
    
    SELECT alias_name as name
    FROM temp_active_ingredients_aliases
    


) 
WHERE MATCH(name) AGAINST 
(

    (SELECT name
    FROM 
    (
        SELECT name 
            FROM active_ingredients

        UNION ALL
        
        SELECT active_ingredients.name as name
        FROM active_ingredients
        INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name

    ) tb3
    GROUP BY name
    HAVING count(*) = 1
    ORDER BY name) tb4

)

The inner queries return the list of active_ingredients.name that do not exactly match at least one of the temp_active_ingredients_alieases.alias_name fields. So that I then try a match of the not exactly matching name(s) with the alias_name(s) by natural language FULLTEXT research. To be noted that the following inner queries are working properly:

    SELECT name
    FROM 
    (
        SELECT name 
            FROM active_ingredients

        UNION ALL
        
        SELECT active_ingredients.name as name
        FROM active_ingredients
        INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name

    ) tbl
    GROUP BY name
    HAVING count(*) = 1
    ORDER BY name

I am quite sure that the syntax error is very stupid, but i cannot see it.

UPDATE 2

Here the links to the code for generating the two tables (schema and some data)

• https://www.dropbox.com/s/81ks3707dve95r9/active_ingredients%20%281%29.sql?dl=0
• https://www.dropbox.com/s/8pph04dmfpdkksh/temp_active_ingredients_aliases%20%283%29.sql?dl=0

Answer 1

Yiu were almost there

To explain a bit further.

1. You put after the match(name) a comma that is wrong at that position.
2. GROUP_CONCAT is there because MySQL expects there a list of words and this was the easiest way to achieve that, for further information about FulltextaSearch and optimization see the manual As you see in the example a space as separator doesn't change anything
3. IN NATURAL LANGUAGE MODE was only a assumption of mine as you wrote research by natural language all other options a also in that link explained

CREATE TABLE table1
(id int,
name text,
FULLTEXT(name))

INSERT INTo table1 VALUES(1,'text1')

CREATE TABLE table2
(id int,
name text)

INSERT INTO table2 VALUES(1,'text1'),(2,'text2')

SELECT t1.id, t1.name, MATCH(t1.name) 
AGAINST 
  (
    (SELECT GROUP_CONCAT(name)
    FROM table2) IN NATURAL LANGUAGE MODE
  ) AS score
FROM table1 t1 
WHERE MATCH(t1.name) 
AGAINST 
  (
    (SELECT GROUP_CONCAT(name)
    FROM table2) IN NATURAL LANGUAGE MODE
  )
  

id | name  |                      score
-: | :---- | -------------------------:
 1 | text1 | 0.000000001885928302414186

SELECT t1.id, t1.name, MATCH(t1.name) 
AGAINST 
  (
    (SELECT GROUP_CONCAT(name SEPARATOR ' ')
    FROM table2) IN NATURAL LANGUAGE MODE
  ) AS score
FROM table1 t1 
WHERE MATCH(t1.name) 
AGAINST 
  (
    (SELECT GROUP_CONCAT(name SEPARATOR ' ')
    FROM table2) IN NATURAL LANGUAGE MODE
  )
  

id | name  |                      score
-: | :---- | -------------------------:
 1 | text1 | 0.000000001885928302414186

db<>fiddle here

I corrected your mistakes

SELECT 
    name,
    MATCH (name) AGAINST ((SELECT 
            name
        FROM
            (SELECT 
                name
            FROM
                active_ingredients UNION ALL SELECT 
                active_ingredients.name AS name
            FROM
                active_ingredients
            INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name) tbl
        GROUP BY name
        HAVING COUNT(*) = 1
        ORDER BY name) ) AS score
FROM
    (SELECT 
        alias_name AS name
    FROM
        temp_active_ingredients_aliases) db2
WHERE
    MATCH (name) AGAINST ((SELECT 
            name
        FROM
            (SELECT 
                name
            FROM
                active_ingredients UNION ALL SELECT 
                active_ingredients.name AS name
            FROM
                active_ingredients
            INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name) tb3
        GROUP BY name
        HAVING COUNT(*) = 1
        ORDER BY name) )

Without proper datqa, i can not test it, in can only remove syntax error

SELECT 
    name,
    MATCH (name) AGAINST ((    SELECT name
    FROM 
    (
        SELECT name 
            FROM active_ingredients

        UNION ALL
        
        SELECT active_ingredients.name as name
        FROM active_ingredients
        INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name

    ) tbl
    GROUP BY name
    HAVING count(*) = 1
    ORDER BY name) ) AS score
FROM
    (SELECT 
        alias_name AS name
    FROM
        temp_active_ingredients_aliases) db2
WHERE
    MATCH (name) AGAINST ((    SELECT name
    FROM 
    (
        SELECT name 
            FROM active_ingredients

        UNION ALL
        
        SELECT active_ingredients.name as name
        FROM active_ingredients
        INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name

    ) tbl
    GROUP BY name
    HAVING count(*) = 1
    ORDER BY name) )