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!

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.