Everybody! This is important. In a few days, these forums will be moving over to using the totally sweet Discourse platform. To ensure this migration happens smoothly with no loss of content, these forums are currently in a read-only mode. I do apologize for the inconvenience.

There is never a good time to turn the forums off for an extended period of time, but I promise the new forums will be a billion times better. I'm pretty sure of it.

See you all on the other side in a few days, and if you have any (non-technical) questions, please e-mail me at kirupa@kirupa.com. For technical questions, try to find a tutorial that corresponds to what you are looking for and post in the comments section of that page.

Cheers,
Kirupa

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