0

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!

1
Contributor
1
Reply
2
Views
8 Years
Discussion Span
Last Post by DiGSGRL
0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.