The forums have permanently moved to forum.kirupa.com. This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.


Results 1 to 2 of 2

Thread: SQL Multi Table search

  1. #1

    SQL Multi Table search

    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

  2. #2
    i would try using a subquery:
    Code:
    SELECT
    	film_title,
    	ID,
    	scoreFilms+scorePeople+scoreReview+scoreGenre AS scoreTotal
    FROM (
    	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+scorePeople+scoreReview+scoreGenre DESC

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012