仅限于相关结果 - MYSQL

SELECT rideid, year, model, rating, SUM(Relevance) as SearchRelevance 
FROM( 
SELECT rideid, year, model, rating, 1 as Relevance FROM cars 
WHERE cat LIKE '%$keyword%' 
UNION ALL 
SELECT rideid, year, model, rating, 1 as Relevance FROM cars 
WHERE cat2 LIKE '%$keyword2%') 
AS t GROUP BY rideid ORDER BY SUM(Relevance) DESC ";

Hi all, I got this awesome query with the help of other members, which works very well in sorting my results based on a relevance system. So that when both of my search criteria are filled those results are ordered first.

The problem is that the results which don't match both criteria (only match 1 criteria) obviously show up in the results, but in a lower order. I want to actually trash those results, is there a way to refine this query so that the only results that come back are the ones that fill both criteria?

Changing the LIKE to = is not an option because the result fields are textareas so mysql needs to search inside the textareas for the keyword.

Thank you

You could just add HAVING SUM(Relevance) = 2 to the outer SELECT, just after the GROUP BY rideid, since Relevance is a count of how many criteria matched. (If you add more criteria later, you'll need to replace the 2 with a larger number to match.)

However, you could also write the whole query in a much simpler way: get rid of the sub-select and UNION ALL, and just do WHERE cat LIKE '%$keyword%' AND cat2 LIKE '%$keyword2%'. The whole point of the sub-select and UNION was to make it possible to get results that match only one or the other.

Am I missing something or you will always getting SUM(Relevance)=2 for records matching both conditions?

Try this:

SELECT rideid, year, model, rating, SUM(Relevance) SearchRelevance
FROM( 
    SELECT rideid, year, model, rating, 1 as Relevance FROM cars 
    WHERE cat LIKE '%$keyword%' 
    UNION ALL 
    SELECT rideid, year, model, rating, 1 as Relevance FROM cars 
    WHERE cat2 LIKE '%$keyword2%'
) AS t 
HAVING SearchRelevance > 1
GROUP BY rideid 
ORDER BY SearchRelevance DESC

    SELECT rideid, year, model, rating, SUM(Relevance) as SearchRelevance 
      FROM( 
        SELECT rideid, year, model, rating, 2 as Relevance FROM cars 
          WHERE cat LIKE '%$keyword%' AND cat2 LIKE '%$keyword2%') 
        AS t GROUP BY rideid ORDER BY SUM(Relevance) DESC