Avatar of proteam4
proteam4 asked on

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

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.
DatabasesMySQL ServerSQL

Avatar of undefined
Last Comment
proteam4

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Guy Hengel [angelIII / a3]

note: you should prefer the JOIN syntax, also:
SELECT L.* 
FROM `DescriptionTable` L
JOIN UserTable U
  ON L.User_id = U.user_id 
WHERE MATCH (book_title, book_content, book_description) AGAINST ('superman')
   OR MATCH (U.user_name) AGAINST ('superman')

Open in new window

ASKER
proteam4

Hi @angelIII

What a silly mistake i was doing, THANKS for helping me, Its working fine, but I am testing this on live environment, if it will work fine then i'll mark ur answer as solution,
THANKS, again :)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23