问跨多个相关表的相关性搜索

EN

SELECT id,SUM(relevance) as total_relevance FROM (
SELECT 
    id, 
    (MATCH(title) AGAINST ('search string')) AS relevance
    FROM Cards
UNION
SELECT 
    Cards.id,
    (MATCH(brigade) AGAINST ('search string')) AS relevance
    FROM Brigades 
    INNER JOIN CardBrigades ON Brigades.id=brigade_id
    INNER JOIN Cards ON card_id=Cards.id 
UNION
SELECT 
    Cards.id,
    (MATCH(identifier) AGAINST ('search string')) AS relevance
    FROM Identifier 
    INNER JOIN CardIdentifier ON Identifier.id=identifier_id
    INNER JOIN Cards on card_id=Cards.id 
) AS combined_search 
GROUP BY id
HAVING total_relevance > 0

select 
    identifier, brigade, P.id as identifier_id, B.id as brigade_id, 
    match(identifier)  against ('purple prince') +
    match(brigade)     against ('purple prince')   as score
from Identifier P, Brigade B
where 
    match(identifier)  against ('purple prince' IN BOOLEAN MODE) and 
    match(brigade)     against ('purple prince' IN BOOLEAN MODE)
order by -score
;