Search code examples
mysqlsqlmany-to-manyrelationship

Mysql join data from three tables (many to many relations)

this is my mysql db schema:

db schema I would like to create query that would return data about all trainings in a following way :

training.*, [type.type], [voivodeship.name] if there would be no type or voivodeship related to given training it should return null in the column value.

For example:

 {
    "id": 1,
    "name": "Example training 2",
    "description": "This is a description 2",
    "status": "status 2",
    "registerFrom": null,
    "registerTo": null,
    "DateOfInsert": "2018-02-06T12:00:57.000Z",
    "training types": "Example type 1,Example type 3,Example type 2"
    "localizations": "loc 1, loc 3"
 },
...
 {
    "id": 99,
    "name": "Example training 99",
    "description": "This is a description 99",
    "status": "status 2",
    "registerFrom": null,
    "registerTo": null,
    "DateOfInsert": "2018-02-06T12:00:57.000Z",
    "training types": null,
    "localizations": null
 },
 {
    "id": 99,
    "name": "Example training 99",
    "description": "This is a description 99",
    "status": "status 2",
    "registerFrom": null,
    "registerTo": null,
    "DateOfInsert": "2018-02-06T12:00:57.000Z",
    "training types": "Example type 9,Example type 4,Example type 2",
    "localizations": "loc 56, loc 32"
  },

this is my current query that returns trainings with information about its localizations (sadly it doesnt return the ones without them) and i also dont have idea how to modify it to return also all types:

SELECT `training`.*, GROUP_CONCAT(`voivodeship`.`name`) AS `Localizations`
FROM `training_localization` 
INNER JOIN `training` ON (`training_localization`.`training_id` = `training`.`id`) 
INNER JOIN `voivodeship` ON (`training_localization`.`voivodeship_id` = `voivodeship`.`id`) 
GROUP BY `training`.`id`

I am not very experienced with sql. Is it even possible with one query?

According to Gordon answer i made new query, at it seems like it's working :):

SELECT t.*, GROUP_CONCAT(vs.name) AS Localizations,  tw.types AS types
FROM training t 
     LEFT JOIN training_localization tl ON tl.training_id = t.id 
     LEFT JOIN voivodeship vs ON tl.voivodeship_id = vs.id
     LEFT JOIN(
        SELECT t.*, GROUP_CONCAT(ty.type) AS Types
        FROM training t 
             LEFT JOIN training_type tt ON tt.training_id = t.id 
             LEFT JOIN type ty ON tt.type_id = ty.id
        GROUP BY t.id
        ) tw ON tw.id = t.id
GROUP BY t.id;
Solution

  • If you want all of something, think "outer join". If you want all trainings, that should be the first table in a series of left joins:

    SELECT t.*, GROUP_CONCAT(vs.name) AS Localizations
FROM training t LEFT JOIN
     training_localization tl
     ON tl.training_id = t.id LEFT JOIN 
     voivodeship vs
     ON tl.voivodeship_id = vs.id
GROUP BY t.id;

    Notes:

    • LEFT JOINs keep everything in the first table and matching rows in the subsequent tables (unless you undo it with an inner join or where clause).
    • Table aliases make the query easier to write and to read.
    • Eliminating unnecessary backticks makes the query easier to write and to read.