Im currently working on a live search and ran into the following problem:

The following query works but i would rather gather "topic" and "threadid" columns from table "bb1_threads" and only column "message" from table "bb1_posts"

SELECT posttopic AS topic, threadid FROM bb".$n."_posts WHERE message LIKE '%$search_string%' OR posttopic LIKE '%$search_string%' LIMIT 1

The following query also works but i cant search the field "message" from table bb1_posts this way:

SELECT topic, threadid FROM bb".$n."_threads WHERE topic LIKE '%$search_string%' LIMIT 1

So what i would like to have and tried to build for hours is a query that:

• selects field "threadid" and "topic" from table "bb1_threads"
• searches field "topic" in table "bb1_threads"
• searches field "message" in table "bb1_posts"
• Sorts the results by best match. (If the search result is closer to the string in column "message" than use that, otherwise the search result from "topic" should be used)

If someone could post a ready to use query, i would really appreciate that! Merry Christmas :)

CodePudding user response：

This may work.

1. search bb1_posts by message and assign 1 as search_rank

2. search bb1_thread by topic and assign 2 as search_rank

3. union results from both, order by search_rank and pick the first row

select threadid  as threadid,
       posttopic as topic,
       1         as search_rank
  from bb1_posts
 where message like '%$search_string%'
 union all
select threadid  as threadid,
       topic     as topic,
       2         as search_rank
  from bb1_threads
 where topic like '%$search_string%'
 order by search_rank
 limit 1;