11

How can I search for in table_a table_b table_c, which have a random number of columns for a string?

I know this is not proper sql but it would be something like:

SELECT * FROM users, accounts, something_else WHERE ->ANY COLUMN CONTAINS 'this_string'<-

Ty in advance for SO community

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
fmsf
  • 36,317
  • 49
  • 147
  • 195
  • Refer this: http://winashwin.wordpress.com/2012/08/28/mysql-search/ –  Aug 30 '12 at 09:26

1 Answers1

30

Add fulltext indexes to all of the string columns in all of those tables, then union the results

select * from table1 where match(col1, col2, col3) against ('some string')
union all
select * from table2 where match(col1, col2) against ('some string')
union all
select * from table3 where match(col1, col2, col3, col4) against ('some string')
...
ʞɔıu
  • 47,148
  • 35
  • 106
  • 149
  • @kiu Very cool - Been looking for this answer for a while now. Just need to know how to ORDER the results by the number of times the string occurs. In my case, I am doing a boolean search in multiple fields from multiple tables. Just need to know how to get the results ordered by relevancy. – G-J Apr 12 '13 at 15:18