Mysql query tag search with relevance

I need help on creating a full text search in multiple columns.

At the end the query should return 'biz_name', first match against 'business' table then match against 'tags' table then select biz_id from 'Business_tags' table then put together all with prioritize results from 'Business Table'.

In easy explanation , when someone search for bank , they should able to get business name that has 'bank' in it and business that tagged with 'bank'.

Please consider the three tables:

Business Table and Tags Table

|biz_id |biz_name      |       |tag_id |tag_name|    
|----------------------|       |----------------|
|001    |Mac burger    |       |101    |cafe    |
|002    |Citi bank     |       |102    |burger  |
|003    |Lou’s Cafe    |       |103    |drink   |
|004    |Baltic pub    |       |104    |bank    |
|005    |T2 Restaurant |       |105    |loan    |

Business_tags Table

|biz_id |tag_id |           
|---------------|       
|001    |102    |       
|002    |105    |       
|002    |104    |       
|003    |101    |       
|003    |103    |       
|004    |103    | 
|005    |102    | 
|005    |101    | 

I can't not use union cause I've different number of columns. Thanks for the help.

---

You can join the three tables together and also do a text search on the name.

SELECT business.biz_name
   FROM business,business_tags,tags
   WHERE business.biz_id = business_tags.biz_id
   AND business_tags.tag_id = tags.tag_id
   AND (business.biz_name LIKE '%bank%' OR tags.tag_name = 'bank')

Will that do what you want?

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

上一篇: Mysql搜索2列和更相关的顺序

下一篇: Mysql查询标记搜索与相关性