I am using the following code to search 4 tables and return a relevance result for each table. I am wondering how I can now add up the scorePeople, scoreReview, scoreFilm and scoreGenre results to obtain one overall result…
SELECT films.ID, films.film_title,
MATCH(films.film_title, films.synopsis, films.trivia, films.awards) AGAINST(‘action’) AS scoreFilms,
MATCH(people.first_name, people.surname) AGAINST(‘action’) AS scorePeople,
MATCH(reviews.review_text) AGAINST(‘action’) AS scoreReview,
MATCH(genres.genre_name) AGAINST(‘action’) AS scoreGenre
FROM films INNER JOIN index_films_genres ON films.ID = index_films_genres.film_ID
INNER JOIN index_films_people ON films.ID = index_films_people.film_ID
INNER JOIN reviews ON reviews.ID = films.ID
INNER JOIN people ON index_films_people.person_ID = people.ID
INNER JOIN genres ON index_films_genres.genre_ID = genres.ID
WHERE MATCH(films.film_title, films.synopsis, films.trivia, films.awards) AGAINST(‘action’)
OR MATCH(people.first_name, people.surname) AGAINST(‘action’)
OR MATCH(reviews.review_text) AGAINST(‘action’)
OR MATCH(genres.genre_name) AGAINST(‘action’)
ORDER BY scoreFilms DESC
Thanks in advance Alex
