mysql query find multiple values in multiple rows?

Question

I have the following case: one question, 5 possible answers to it. database has 5 columns for it where each column obviously can hold every answer in it, but always one-at-a-time and every answer only occurs in one column at-a-time.

for instance i have (simplified with language names for explain purposes):

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

how would i go about when i want to search for rows where language is both english and greek?

i'd rather not use

'WHERE english in (language1, language2, language3, language4, language5) 
 AND greek in (language1, language2, language3, language4, language5)';

as this is only one question in the form and as you see every question can have a lot of answers; i have 65+ questions so the query would become enormous...

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

Is something like this possible and if so, how?

Thanks for any thoughts, hints or tips!

can you share your sample table structure or some data with structure — Lakshmi, Jun 20 '14 at 06:30
That's the way to do it (with the "and" clause), that said, and it depends on your needs, but an intermediary datatable with a 1 to n relationship would perhaps be more efficient. The other datable would hold the id of the item and the ids of all the languages associated to that item. — netadictos, Jun 20 '14 at 06:36
Thank you for your comments and i think i'll go with the one netadictos posted. it's the only way to do this decently i think. hopefully i'll get it right this time :-) — Dave33, Jun 21 '14 at 08:12

FuzzyTree · Answer 1 · 2014-06-20T07:09:07.527

Pivot your columns into rows using UNION then select from the derived table where any row is in your set of desired languages.

The HAVING COUNT(*) clause specifies the number of languages that must be matched from your where clause. In the example below 2 means all returned ids will have both English and Greek as answers.

SELECT id
FROM
(
    SELECT id, language1 language from table1
    UNION
    SELECT id, language2 language from table1
    UNION
    SELECT id, language3 language from table1
    UNION
    SELECT id, language4 language from table1
    UNION
    SELECT id, language5 language from table1
) t1 WHERE language IN ('English','Greek')
GROUP BY id
HAVING COUNT(*) = 2

Thanks for the help! i have looked at it and although it will help me along, if i have many multiple choice questions your code will make too long of a select query :-( but it has given me some ideas again so thx! — Dave33, Jun 21 '14 at 08:14

mysql query find multiple values in multiple rows?

1 Answers1