Re: Full Text search on multiple tables?

[Salomon Valverde]

Hello James,
I took your advise on this thread and created my query with the OR MATCH statements, which the query logically seems correct, but I am getting an error that is driving me crazy, Can you or anyone out there in MySQL land please give me a hand on this?? My PHP query statement is as follows:

$sql = "SELECT rt.first_name,rt.last_name,rt.agent,rn.note, MATCH(rt.first_name) AGAINST('%$keyword%') OR MATCH(rt.last_name) AGAINST('%$keyword%') OR MATCH(rt.agent) AGAINST('%$keyword%') AS score FROM rose_tickets AS rt LEFT JOIN rose_notes AS rn ON rt.id = rn.ticket_id WHERE MATCH(rn.note) AGAINST('%$keyword%')";

Im getting the following error:

Can't find FULLTEXT index matching the column list


Also Im not sure if I'm suppose to be passing the MATCH() function the table alias names, ex. MATCH(rt.first_name)... or if it's suppose to be exactly the field names in my index. Although I think the error that it's throwing me has nothing to do with this situation.

Thanks in Advance.

Sal