swto — 2010-01-22T00:28:20-05:00 — #1
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
philiptoop — 2010-01-22T03:27:24-05:00 — #2
What you want is a UNION. Forms a recordset from two select statements.
swto — 2010-01-22T11:27:33-05:00 — #3
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 (?).
philiptoop — 2010-01-22T11:45:17-05:00 — #4
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.
swto — 2010-01-22T11:53:52-05:00 — #5
So both columns in each select statement must be, for example, TEXT ?
Is this what you mean by the same "data type"?
philiptoop — 2010-01-22T12:03:38-05:00 — #6
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.
guido2004 — 2010-01-22T12:05:16-05:00 — #7
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.
swto — 2010-01-22T12:24:19-05:00 — #8
Thanks to both of you - I'll start working on my script...