Mysql select with ID's from different tables

Question

Ive got a couple of tables, exhibitions, languages, forms and questions. The questions have a lang_id and form_id in their table, and the exhibitions have got a form_id in the table. I want to select the languages which are linked to the forms. The only thing that is known is the exhibition id. How this is done?

You can get the form_id from the exhibitions table, with the known exhibition id. Then you can select the questions from that form_id and with that data you can check which languages are linked to the questions. But how do I accomplish this query?

Here is a sqlfiddle: http://sqlfiddle.com/#!9/bb726

The query I got is this one:

SELECT f.id, f.name  
FROM app_languages f,
     app_exhibition b,
     app_vragen_translations v 
WHERE f.id = b.form_id
  AND b.id = 4
  AND v.lang_id;

app_vragen is not included in the sqlfiddle, only app_vragen_translation — VolkerK
– VolkerK, Commented Jul 9, 2015 at 7:49

Cyril Gandon · Accepted Answer · 2015-07-09 08:33:05Z

2

I didn't use your fiddle because you describe the problem so well that the query is so simple.

Your need to build name of your table and column better than that. With a good looking schema, the query you describe is this one:

SELECT DISTINCT lang.id, lang.name  
FROM exhibition
    INNER JOIN form
        ON exhibition.formId = form.id
        INNER JOIN question
            ON form.Id = question.formId
            INNER JOIN translation
                ON translation.formId = form.id
                INNER JOIN lang
                    ON translation.langId = lang.id
WHERE exhibition.id = 4

Here is the query with the fiddle :

SELECT DISTINCT app_languages.id, app_languages.name
FROM app_exhibition
    INNER JOIN app_forms
        ON app_exhibition.form_id = app_forms.id
        INNER JOIN app_vragen
            ON app_forms.id = app_vragen.form_id
            INNER JOIN app_vragen_translations
                ON app_vragen_translations.vraag_id = app_vragen.id
                INNER JOIN app_languages
                    ON app_vragen_translations.lang_id = app_languages.id
WHERE app_exhibition.id = 4

Results app_exhibition.id = 4

id  name
4   German

More results if using the other exhibition app_exhibition.id = 5

id  name
7   Dutch
2   English

Some hints :

Use plain english and not your local langage. There is plenty reason for that, one of them is that it is easier to have answer on stackoverflow
Don't use implicit joins (joins in the WHERE clause), but use JOIN clause instead. The query becomes more readable, and the difference between JOINING and FILTERING is obvious. JOIN is for joining, WHERE is for filtering. WHERE is NOT for joining.

Note : exhibition and question are referencing the field formId, so the join on the table form can be omitted if no further info are needed from this table:

SELECT DISTINCT app_languages.id, app_languages.name
FROM app_exhibition
    INNER JOIN app_vragen
        ON app_exhibition.form_id = app_vragen.form_id
        INNER JOIN app_vragen_translations
            ON app_vragen_translations.vraag_id = app_vragen.id
            INNER JOIN app_languages
                ON app_vragen_translations.lang_id = app_languages.id
WHERE app_exhibition.id = 4

edited Jul 9, 2015 at 8:33

answered Jul 9, 2015 at 7:54

Cyril Gandon

17.1k15 gold badges84 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kuubs Over a year ago

Thanks for the tips! Yeah, I was in the process of renaming my code to use English instead of Dutch.

Kuubs Over a year ago

About the answer, the query is a bit messy because you used the wrong table names. But I think your answer works as well...

Cyril Gandon Over a year ago

I forgot one table, it should be better now!

Kuubs Over a year ago

I marked your answer as right because you was right after all.

Collectives™ on Stack Overflow

Mysql select with ID's from different tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related