Я пытаюсь возвратить СООТВЕТСТВИЕ () ПРОТИВ () результаты против нескольких таблиц с помощью ОБЪЕДИНЕНИЙ, единственной проблемой является некоторый возврат строк уместность 0, я хочу исключить их. После объединений там способ использовать 'ГДЕ уместность> 0'
Ниже немного моего SQL
SELECT pages.content AS search, page_info.url AS link, MATCH(pages.content) AGAINST('Wales') as relevance
FROM page_content
LEFT JOIN pages ON (page_info.page = pages.id)
UNION
SELECT products_real.name AS search, products_real.event AS link, MATCH(products_real.name, products_real.description) AGAINST ('Wales') as relevance
FROM product_real
ORDER BY relevance DESC
Так есть ли так или иначе, я могу добавить ГДЕ уместность> 0 перед ORDER BY
большое спасибо
Попробуйте использовать производную таблицу
SELECT *
FROM
(SELECT
pages.content AS search,
page_info.url AS link,
MATCH(pages.content) AGAINST('Wales') as relevance
FROM page_content
LEFT JOIN pages ON (page_info.page = pages.id)
UNION
SELECT
products_real.name AS search,
products_real.event AS link,
MATCH(products_real.name, products_real.description) AGAINST ('Wales') as relevance
FROM product_real) myQuery
Where myQuery.relevance <> 0
ORDER BY myQuery.relevance DESC
вы можете добавить его в каждый запрос или в конце:
SELECT pages.content AS search, page_info.url AS link, MATCH(pages.content) AGAINST('Wales') as relevance
FROM page_content
LEFT JOIN pages ON (page_info.page = pages.id)
WHERE relevance > 0
UNION
SELECT products_real.name AS search, products_real.event AS link, MATCH(products_real.name, products_real.description) AGAINST ('Wales') as relevance
FROM product_real
WHERE relevance > 0
ORDER BY relevance DESC
SELECT search, relevance FROM
(
SELECT pages.content AS search, page_info.url AS link, MATCH(pages.content) AGAINST('Wales') as relevance
FROM page_content
LEFT JOIN pages ON (page_info.page = pages.id)
UNION
SELECT products_real.name AS search, products_real.event AS link, MATCH(products_real.name, products_real.description) AGAINST ('Wales') as relevance
FROM product_real
) D
WHERE relevance > 0
ORDER BY relevance DESC