SQL query with SubQuery on the relation fields by id-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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);