search on a table and linked tables with fulltext

I have a simple database like this:

table "contents":
--------------------------
CID | contentName
  1 | Watermelon

table "tags":
--------------------------
TID | tagName | tagKeywords
 77 | Fruit   | Fruit, fruits
 99 | Nature  | nature, green, mother earth

table "associations":
--------------------------
CID | TID
  1 | 77
  1 | 99

how should I write a select that searches in the table "contents" with every row inheriting any associated tag's keywords?

for instance if i search "fruit" and "green" the fulltext search should result more relevant because matches two tags

---

I think this is more what you're looking for:

SELECT c.contentName, GROUP_CONCAT(tagkeywords separator ',')
FROM contents c
INNER JOIN associations a ON a.CID = c.CID
INNER JOIN tags t ON t.TID = a.TID
GROUP BY c.contentName;

I just tested it, and got the following:

contentName    GROUP_CONCAT(tagkeywords separator ',')
-----------    ---------------------------------------
Watermelon     Fruit, fruits,nature,green,mother earth

链接地址: http://www.djcxy.com/p/75280.html

上一篇: 比较关联分数来自不同表格的mySQL全文搜索

下一篇: 用全文搜索表格和链接的表格