MySQL Forums
Forum List  »  Full-Text Search

full text search in 2 tables
Posted by: Maarten Menten
Date: July 21, 2010 11:30AM

Hi,

I want to do a full text search in two tables:
- articles ( fields to search: title, content )
- pages ( fields to search: title, content ) This table holds the pages of an article.

I want to fetch a record if the search matches the content in 1 of the 4 fields

The sql for so far:

SELECT t1.*, t2.*
FROM articles AS t1
LEFT JOIN pages AS t2 ON t2.article_id = t1.id
WHERE
MATCH ( t1.title, t1.content ) AGAINST ('Hello world!') OR
MATCH ( t2.title, t2.content ) AGAINST ('Hello world!')
AND t1.online='1'
ORDER BY t1.id, t2.page_index

is this a correct approach?

I'm also trying to find a way to sort on the searchoccurence (score):

article1 (higher score than article2)
-page1 (higher score than page2)
-page2

article2
-page1
-page2



regards



Edited 1 time(s). Last edit at 07/21/2010 11:35AM by Maarten Menten.

Options: ReplyQuote


Subject
Views
Written By
Posted
full text search in 2 tables
4183
July 21, 2010 11:30AM
2064
July 26, 2010 11:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.