Union Site Search with Relevance
 

I am building a site search that involves a union of two tables in two databases and needs to have relevancy factored in.
I do not want to use a full-text or boolean search.

I can search one database and one table using relevance fine. I can union the two tables and search without relevance fine. When I put relevance on both tables with the union it no longer works.

Here is the query with union that works:

(SELECT gameID,title FROM searchTest.games 

WHERE((title LIKE '%$searchTerm1%' or author LIKE '%$searchTerm1%' or description LIKE '%$searchTerm1%')))

UNION 

(SELECT postID,title FROM searchTest2.posts WHERE((title LIKE '%$searchTerm1%' or username LIKE '%$searchTerm1%' or content LIKE '%$searchTerm1%') ))

Here is the query on one table with relevance that works:

SELECT gameID,title, ((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))AS relevance FROM games WHERE(title LIKE '%$searchTerm1%' or author LIKE '%$searchTerm1%' or description LIKE '%$searchTerm1%')AND locked=0 ORDER BY relevance DESC

Here is the query with union and relevance that does not work:

SELECT gameID,title,

 ((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))

AS relevance FROM games WHERE

(((title LIKE '%$searchTerm1%' or author LIKE '%$searchTerm1%' or description LIKE '%$searchTerm1%'))AND locked=0)

UNION 

SELECT postID,title,

((2*(title LIKE '%$searchTerm1%'))+(1.5*(author LIKE '%$searchTerm1%'))+(1*(description LIKE '%$searchTerm1%')))

AS relevance FROM searchTest2.posts WHERE

(((title LIKE '%$searchTerm1%' or username LIKE '%$searchTerm1%' or content LIKE '%$searchTerm1%'))AND locked=0)

I wouldn't be surprised if I did something really silly and screwed up a paren or something. I have been working on this for days and just can't seem to find the mistake that is keeping the union with relevance from running.
Thank you for looking and I hope someone can help!

Re: Union Site Search with Relevance
 

A couple more hours of staring at the screen and rewriting the statements have finally solved the problem. Both of the select statements needed to be encased in parens.