0

Hello all I ask help regarding use of Full text in mysql. I have two tables. One where there are keywords and another where there are other keywords. I would like to create a relevance between the two joining tables getting the highest importance. For example if in a table I as the keyword "A, B, C", "B, A, F" and in the other I "B, C, D" I would like to obtain in a single table from me as a result the relevance between these two tabelle.I hope I was as clear as possible.

Table A

+------------+------------+
| id         |  keyword1  |
+------------+------------+
| 1          | A,B,C      |
| 2          | B,A,F      |
+------------+------------+

Table B

+------------+------------+
| id         |  keyword2  |
+------------+------------+
| 1          | B,C,D      |
+------------+------------+

I Use this sql code:

SELECT id,keyword1, 
    MATCH (B.keyword2) AGAINST (+'B,C,D' IN BOOLEAN MODE) AS Relevance1   
         FROM B
    UNION
     SELECT id,keyword2, 
    MATCH (B.keyword2) AGAINST (+'B,C,D' IN BOOLEAN MODE) AS Relevance1
    FROM B
    WHERE (MATCH (A.keyword1) AGAINST (+'B,C,D' IN BOOLEAN MODE))
    ORDER BY (Relevance1) DESC

I want the result like this:

+------------+------------+-------------+-------------+
| id         |  keyword2  |   keyword1  |  Relevance1 |
+------------+------------+-------------+-------------+
| 1          | B,C,D      |   A,B,C     |  0,1213     |
+------------+------------+-------------+-------------+
| 2          | B,C,D      |  B,A,F      !  0,01234    |
+------------+------------+-------------+-------------+
Zrufy
  • 423
  • 9
  • 22
  • Please provide the table structures and some example data from both tables... and a example of expected result. – Raymond Nijland Feb 25 '17 at 10:55
  • I edited the post hope I was clear – Zrufy Feb 25 '17 at 11:16
  • you won't be able to achieve this with a single `SELECT` query because `AGAINST()` requires explicit string value, table column reference will not work. – andrews Feb 27 '17 at 20:05
  • I changed my table with a join to obtain the result that interested me thank you all for the answers.if interested i post the table. – Zrufy Feb 28 '17 at 04:12

0 Answers0