Is it possible to write two "from" clauses in a select statement, so that the search queries two tables? Or, do I need a join statement in here?
The submitted form in html has two text input boxes for keywords. I want each input box to search different tables.
"SELECT * FROM table_1 WHERE MATCH(column) AGAINST("word_1" IN BOOLEAN MODE)
AND SELECT * FROM table_2 WHERE MATCH(column) AGAINST("word_2" IN BOOLEAN MODE)"
thanks in advance
What you want is a UNION. Forms a recordset from two select statements.
Thanks, Philip -
So my statement should look something like:
(SELECT * FROM table_1 WHERE MATCH(column) AGAINST("word_1*" IN BOOLEAN MODE))
(SELECT * FROM table_2 WHERE MATCH(column) AGAINST("word_2*" IN BOOLEAN MODE))
I am assuming that this will work for full-text searching (?).
Don't believe you need the brackets around the SELECTs.
There are restriction on UNIONs in that you are forming a single recordset so the columns from the two tables should have the same data type.
Can't imagine a reason that full-text searching should be a problem.
So both columns in each select statement must be, for example, TEXT ?
Is this what you mean by the same "data type"?
They can be any data type as long as they are the same data type. So if one is TEXT the other must be TEXT as well.
Both SELECTs must return the same number of columns as well (I know that this is implicit in your 'same type' rule, but it can't hurt saying it explicitly )
The best thing is not to use '*', but name each column. That way, if for some reason in the future a column is added to one of the tables, the UNION will continue to work.
Thanks to both of you - I'll start working on my script...