开发者

Two full text mysql searches with union but return a distinct field

开发者 https://www.devze.com 2023-01-22 16:18 出处:网络
SELECT DISTINCT sectionID FROM ( (SELECT sectionID,MATCH (content) AGAINST (\'blah\') AS relevance FROM sectioncontent WHERE MATCH (conte开发者_如何转开发nt) AGAINST (\'blah\')) AS alias
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
0

精彩评论

暂无评论...
验证码 换一张
取 消