I am using MySQL 4.x version, I am trying to put searching (FULLTEXT) on more than one table but i am not getting proper result and also i am not able to figure out how to make the query??? The details are as following.
I am having two tables,
DescriptionTable, Which have following fields,
and UserTable, which have
Now i have to make a searching mechanism which will search the book in DescriptionTable, Like suppose i want to search a book name "Superman" So it will search in Book_Title, Book_Description and Book_Contents fields and wherever it will find the word "Superman" it will display that record.
I have to search the same in user table. Meaning suppose user "mike" created two books then if i'll enter search criteria as mike then it will display the books which have "mike" word BUT also it should display all the books created by mike, I am having only one search box, So i am not able to seprate my searches like BookSearch and UserSearch, I have to combine both these type of searches in one. Meaning user will search everthing from one box and it should display the relavent records by searching them in both tables.
Suppose I searched, "9/11 Attacks" which is not a user name, but it will search this in both tables, whatever records found in DescriptionTable , it will display that and it will also search this text in user table if found the user name of "9/11 Attacks" then it should display all records of that user no matters user have the word "9/11 Attacks" in his/her Book_Title, Book_Decription or in Book_Contents.
I hope this is enough to understand what i want. I have also tried by making join on the basis of user_id caz it present in both tables. but that's not giving proper result. I have made following queries,
Query when i was not required the user_name to search >>
SELECT * FROM `DescriptionTable` WHERE MATCH (book_title, book_content, book_description) AGAINST ('superman')
And query which i have tried but not succeeded when i required the user name to search >>
SELECT L.* FROM `DescriptionTable` L, UserTable U
WHERE L.User_id = U.user_id AND MATCH (book_title, book_content, book_description) AGAINST ('superman')
MATCH (U.user_name) AGAINST ('superman')
So please if anyone can make a query for me, according to my problem.