Two full text mysql searches with union but return a distinct field
SELECT DISTINCT sectionID FROM (
(SELECT sectionID,MATCH (content) AGAINST ('blah') AS relevance
FROM sectioncontent WHERE MATCH (conte开发者_如何转开发nt) AGAINST ('blah')) AS alias
UNION DISTINCT
(SELECT sectionID,MATCH (name, description) AGAINST ('blah') AS relevance
FROM sections WHERE MATCH (name, description) AGAINST ('blah'))
)
I would eventually use GROUP BY and ORDER BY but I can't get the above to work. Any point to the right direction would be much appreciated, thanks!
This is what I was originally using, but it would return two results with same sectionID (one from the section table and one from the sectioncontent table) since the relevance fields were not the same.
(SELECT sectionID,MATCH (content) AGAINST ('blah') AS relevance
FROM sectioncontent WHERE MATCH (content) AGAINST ('blah'))
UNION DISTINCT
(SELECT sectionID,MATCH (name, description) AGAINST ('blah') AS relevance
FROM sections WHERE MATCH (name, description) AGAINST ('blah'))
ORDER BY relevance DESC
sectionID relevance
32 6.42576837539673
32 2.10306763648987
Figured it out, here is the solution for anyone else who may come across this
SELECT sectionID,sum(relevance) FROM (
SELECT sectionID,MATCH(content) AGAINST ('blah') as relevance
FROM sectioncontent WHERE MATCH(content) AGAINST ('blah')
UNION
SELECT sectionID ,MATCH(name,description) AGAINST ('blah') as relevance
FROM sections WHERE MATCH(name,description) AGAINST ('blah')
) as tmptable GROUP BY sectionID ORDER BY relevance DESC
精彩评论