Mysql select with id from different tables
I have multiple tables, exhibitions, languages, forms and questions. Questions are lang_id
also form_id
in their table, and at exhibitions they are in the table form_id
. I want to select the languages associated with forms. The only thing that is known is the exhibition ID. How it's done?
You can get form_id from exhibitions table with known exhibition id. Then you can select questions from that form_id
, and with that data, you can check which languages are associated with the questions. But how do I fulfill this request?
Here is the sqlfiddle: http://sqlfiddle.com/#!9/bb726
I got a request:
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;
source to share
I didn't use your fiddle because you describe the problem so well that the request is so simple.
Your need to build your table and column name is better than that. With a nice outline, the query you describe is as follows:
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 a request with a 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
Other results if you are using a different exhibition app_exhibition.id = 5
id name
7 Dutch
2 English
Some hints:
- Use plain English, not your local language. There are many reasons for this, one is that it is easier to answer on stackoverflow
- Don't use implicit joins (joins in the WHERE clause), but use the JOIN clause instead. The query becomes more readable and the difference between JOINING and FILTERING is obvious. JOIN for join, WHERE for filtering. WHERE NOT to join.
Note: exhibition
and question
refer to a field formId
, so the join in the table form
can be omitted if no additional information is needed in that 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
source to share