Question 113270 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL query with SubQuery on the relation fields by id

I would like to receive a data object country. An object country has the following attributes (id, continent_id, language_id).

Table countries {
  id, name, continent_id, language_id
}

Table continents {
  id, name
}

Table languages {
  id, name
}

Instead of getting a DataObject containing only the continent_id and language_id, I want to get the name of the continent and the language. Like that:

{
  id: 1,
  name: Germany,
  continent_id: 1,
  language_id: 1,
  continent: "europe", // new field
  language: "german" // new field
}

How can I achieve this?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found a simple solution for my case. Maybe not the smartest but it works. There are probably better solutions. I don't know what influence the size of the data set will have on the performance. I still have to test this.

    SELECT c.*, co.name as continent, l.name as language 
FROM countries c  
JOIN continents co ON co.id = c.continent_id 
JOIN languages l ON l.id = c.language_id
WHERE c.id IN (1,2,3);

  2. 0

    you need to join the two additional tables to the man countries

    If every country has only one language, this will be enough to INNER JOIN them.

    with multiply languages, you need to GROUP BY and use GROUP_CONCAT or the languages

    SELECT
    countr.id, contr.name, continent_id, language_id 
    , co.name as continent
    , lang.name as language
FROM countries  countr 
INNER JOIN continents co ON contr.continent_id = co.id
INNER JOIN languages  lang ON lang.id = contr.language_id

    If a country has multiple languages

    SELECT
    countr.id, contr.name, continent_id, language_id 
    , co.name as continent
    , GROUP_CONCAT(lang.name) as languages
FROM countries  countr 
INNER JOIN continents co ON contr.continent_id = co.id
INNER JOIN languages  lang ON lang.id = contr.language_id 
GROUP BY countr.id, contr.name, continent_id, language_id,co.name
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search