troubleshooting Question

How to put FULLTEXT search on morethan one table???

Avatar of proteam4
proteam4 asked on
DatabasesMySQL ServerSQL
3 Comments1 Solution375 ViewsLast Modified:
Hi,
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,
Id
User_id
Book_Title
Book_Description
Book_Content

and UserTable, which have
User_id
User_Name
user_password

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.
BUT
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')
OR
MATCH (U.user_name) AGAINST ('superman')


So please if anyone can make a query for me, according to my problem.

Thanks.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros