3

I have 2 tables table1 and table2. Table1 consist of paragraph which is a news article including some company codes/names, while table2 is a dictionary consist of 2 columns which are company code and the company name.

Using MYSQL FTS function MATCH i want to compare the article with all items/dictionary in table2 ( both of columns )

SELECT * FROM pyproject.table1 WHERE MATCH (Paragraph) against
(SELECT * FROM pyproject.table2' with query expansion);

It seems to produce an error

How to check whether the article matched with the dictionary, the data in table1 will be selected and the company code column in table2 is included/joined together with the data in table1.

So if the article in table1 consist of multiple company codes, it will be duplicated and added appropriate company codes

FH337
  • 69
  • 7
  • 1
    See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Alberto Moro Jul 03 '19 at 07:13
  • Any particular reason why you don't want to type in two columns of table2 statically? – Nae Jul 03 '19 at 07:26

1 Answers1

1

According to the reference that you've provided AGAINST doesn't work with columns. Instead you can use LIKE:

SELECT
    t1.Paragraph,
    t2.CompanyCode
FROM pyproject.table1 t1
INNER JOIN pyproject.table2 t2 ON (
    (t1.Paragrah LIKE CONCAT('%', t2.CompanyCode, '%')) OR
    (t1.Paragrah LIKE CONCAT('%', t2.CompanyName, '%'))
    )
;
Nae
  • 14,209
  • 7
  • 52
  • 79
  • 1
    I tried your query and yield an error "Incorrect argument to AGAINST" , referring to this post https://stackoverflow.com/questions/10465758/subquery-incorrect-arguments-to-against-using-mysql . It seems impossible to match against a column. – FH337 Jul 03 '19 at 09:21
  • @FH337 Thanks for your information. I've updated the answer to provide an alternative solution. – Nae Jul 03 '19 at 09:39
  • it works like charm sir, thank you. I add WHERE t2.CompanyCode IS NOT NULL OR t2.CompanyName IS NOT NULL;. So it will only return the article which has the company code or company name – FH337 Jul 03 '19 at 10:08
  • @FH337 Hm, I think you should rather use `INNER JOIN` then. It filters out only the matched rows in both tables. Updated the answer accordingly. – Nae Jul 03 '19 at 10:12