Two "From" Clauses in Select Statement-help

Databases
1

Hi All,

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.

My example:

"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

2

What you want is a UNION. Forms a recordset from two select statements.

3

Thanks, Philip -

So my statement should look something like:

(SELECT * FROM table_1 WHERE MATCH(column) AGAINST("word_1*" IN BOOLEAN MODE))
UNION
(SELECT * FROM table_2 WHERE MATCH(column) AGAINST("word_2*" IN BOOLEAN MODE))

I am assuming that this will work for full-text searching (?).

Cheers

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.

5

So both columns in each select statement must be, for example, TEXT ?

Is this what you mean by the same “data type”?

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.

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 :slight_smile: )

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.

8

Thanks to both of you - I’ll start working on my script…

Cheers!