sql - mysql query find multiple values in multiple rows? -

sql - mysql query find multiple values in multiple rows? -

i have next case: 1 question, 5 possible answers it. database has 5 columns each column can hold every reply in it, one-at-a-time , every reply occurs in 1 column at-a-time.

for instance have (simplified language names explain purposes):

language1 language2 language3 language4 language5 ----------+-----------+---------+-----------+---------- english language french high german greek polish

how go when want search rows language both english language , greek?

i'd rather not utilize

'where english language in (language1, language2, language3, language4, language5) , greek in (language1, language2, language3, language4, language5)';

as 1 question in form , see every question can have lot of answers; have 65+ questions query become enormous...

i'm searching solution 'where (english, greek) in (language1, language2, language3, language4, language5)', although gives error using (english, greek).

is possible , if so, how?

thanks thoughts, hints or tips!

pivot columns rows using union select derived table row in set of desired languages.

the having count(*) clause specifies number of languages must matched clause. in illustration below 2 means returned ids have both english , greek answers.

select id ( select id, language1 language table1 union select id, language2 language table1 union select id, language3 language table1 union select id, language4 language table1 union select id, language5 language table1 ) t1 language in ('english','greek') grouping id having count(*) = 2

mysql sql where