CODEWITHSUNDEEP
mysqlsql
Kuubs
Kuubs

Reputation: 1340

Mysql select with ID's from different tables

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;

Upvotes: 3

Views: 1341

Answers (1)

Cyril Gandon
Cyril Gandon

Reputation: 17058

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

Upvotes: 2

Related Questions