Find Name Or Alias Of Matched Column In Match() Against()

I am using MATCH() AGAINST() for full text search in my database. As since i wanted data from multiple tables, i created a VIEW and am using following format of query in MARIADB, S

Solution 1:

You can use a case expression in the select:

SELECT DAT1, DAT2, DAT3,
       CONCAT_WS(',',
                 (CASEWHENMATCH(COL1) AGAINST ('lisa mona aachen'INNATURALLANGUAGE MODE) >0THEN'col1'END),
                 (CASEWHENMATCH(COL2) AGAINST ('lisa mona aachen'INNATURALLANGUAGE MODE) >0THEN'col2'END),
                 (CASEWHENMATCH(COL2) AGAINST ('lisa mona aachen'INNATURALLANGUAGE MODE) >0THEN'col3'END)
                ) as cols
FROM VIEW1
WHEREMATCH (COL1) AGAINST ('lisa mona aachen'INNATURALLANGUAGE MODE) >0ORMATCH (COL2) AGAINST ('lisa mona aachen'INNATURALLANGUAGE MODE) >0ORMATCH (COL3) AGAINST ('lisa mona aachen'INNATURALLANGUAGE MODE) >0;

Solution 2:

( SELECT DAT1
    FROM tbl1
    WHEREMATCH (COL1) AGAINST ('lisa mona aachen'INNATURALLANGUAGE MODE) )
UNIONALL
( SELECT DAT2
    FROM tbl2
    WHEREMATCH (COL2) AGAINST ('lisa mona aachen'INNATURALLANGUAGE MODE) )
UNIONALL    
( SELECT DAT3
    FROM tbl3
    WHEREMATCH (COL3) AGAINST ('lisa mona aachen'INNATURALLANGUAGE MODE) )

UNION ALL is faster than the default (UNION DISTINCT), but use DISTINCT if there could be dups and you want them to be de-dupped.

No VIEW needed. Or, you could make the above a VIEW with the text string waiting to be filled in (via an @variable?).