Using a general WHERE clause when using UNIONS in sql

Question

I am trying to return MATCH () AGAINST () results to multiple tables using UNIONS, the only problem is that some rows return relevancy 0, I want to exclude them. After unions can use 'WHERE relevance> 0'

Below is a bit of my 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 

So, I can add the value WHERE> 0 to ORDER BY

many thanks

Answer 1

Try using a view

 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 

Answer 2

You can add it to each request or at the end:

 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