MySQL Multi Table Search

Question

table1
 ID
 SUBJECT
 CONTENT

table2
 ID
 SUBJECT
 CONTENT

table3
 ID
 SUBJECT
 CONTENT

... 5 more

I want to search SUBJECT on all the tables

How can I do this?

Answer 1

select * from table1 where subject like '%match%' union
select * from table2 where subject like '%match%' union
select * from table3 where subject like '%match%' union
select * from table4 where subject like '%match%' union
select * from table5 where subject like '%match%' union
select * from table6 where subject like '%match%' union
select * from table7 where subject like '%match%' union
select * from table8 where subject like '%match%'

Answer 2

CREATE VIEW multitable AS
SELECT * FROM table1
UNION SELECT * from table2
UNION SELECT * from table3;

SELECT subject FROM multitable ...

Answer 3

Because all the tables have the same syntax, you can use the UNION operator.

SELECT * FROM Table1
UNION Table2
UNION Table3
UNION Table4
UNION Table5
UNION Table6
UNION Table7
UNION Table8
WHERE SUBJECT="Subject"

For the sake of simplicity, 8 tables isn't too much to write out. If you had more, I'd recommend a dynamic query.