CODEWITHSUNDEEP
sql
meetpd
meetpd

Reputation: 9219

How to create Select statement which searches in whole table instead of single column?

I have 5 columns in a database table.

Column names are: English, French, German, Spanish, Russian.

I want to create select statement which searches for a word in the whole table (including all columns) instead of just one column, e.g.:

select * from language
where English = "Parrot"

The query above only searches "English" column for word "Parrot".

I want a select statement which searches for "Parrot" in all columns.

How do I do that?

Upvotes: 0

Views: 352

Answers (5)

abatishchev
abatishchev

Reputation: 100268

You need unpivot the table and then do a regular where-search

Upvotes: 1

Mikko Wilkman
Mikko Wilkman

Reputation: 1535

MySQL isn't AFAIK very good at optimizing ORs, and with that approach your solution won't be very easy to maintain if you decide to add new languages in the future.

I'd definitely change the table structure to a normalized one.

Upvotes: 0

ajreal
ajreal

Reputation: 47321

Beside the OR (I just fall in love with find_in_set)

select 
find_in_set('Parrot', 
  concat(English, ',', French, ',', German, ',', Spanish, ',', Russian)
) as pos
from language
having pos<>0

And the column pos can indicate which language match the search term.

However, normalize is the better solution

Upvotes: 0

nan
nan

Reputation: 20296

The simples solution would be:

Select * from language where English = "Parrot"
OR French = "Parrot"
OR German = "Parrot"
OR Spanish = "Parrot"
OR Russian = "Parrot"

This solution may lack scalability. If you are planning to expand table by adding new columns you write your own function to perform the searching.

Here is a sample stored procedure that searches whole database for a specified keyword, it might be interesting for you.

Upvotes: 0

Nikita Rybak
Nikita Rybak

Reputation: 68006

I don't think you'll get anything better than plain or

Select * from language where English = "Parrot" or French = "Parrot" or German = "Parrot"

Otherwise, you could move individual translations into a separate table. So, one row in language will have not five strictly set columns, but five (or any other number) associated rows in a language_word table.

Upvotes: 0

Related Questions