LEFT JOIN query returns only results existing in table 2-CodePudding

I would like to improve an existing (php/mysql) codebase that presents an overview of choir attendances (X axis: dates, Y axis: students). The data is spread across various tables (and a very perfectible naming logic, as you will see). The query is quite heavy and to be repeated for each "chorist" so I am trying to construct a VIEW to optimize the backend code.

The involved tables:

chorale_dates = the rehearsal dates for the season
chorale_inscriptions = the chorists' registrations
chorale_presences = the recorded absences (chorists attendances)
chorale_id = the chorists' personal details (email, name, ...)

┌───────────────────────┐      ┌──────────────────────────────────┐  ┌───────────────────────┐
│ chorale_dates         │      │chorale_presences                 │  │ chorale_inscription   │
├───────────────────────┤      ├──────────────────────────────────┤  ├───────────────────────┤
│                       │      │                                  │  │                       │
│ chorale_date_id       │      │chorale_presences_id            ┌─┼──┤►chorale_inscription_id│
│              ▲        │      │                                │ │  │                       │
│              │        │      │                                │ │  │                       │
│              │        │      │                                │ │  │                       │
│              └────────┼──────┤chorale_presences_date_id       │ │  │ chorale_id_id         │
│                       │      │                                │ │  │ │                     │
│                       │      │chorale_presences_inscription_id│ │  │ │                     │
│                       │      │                                  │  │ │                     │
│                       │      │                                  │  │ │                     │
└───────────────────────┘      └──────────────────────────────────┘  └─┼─────────────────────┘
                                                                       │
                                                                       │            ┌───────────────────────┐
                                                                       │            │ chorale_id            │
                                                                       │            ├───────────────────────┤
                                                                       │            │                       │
                                                                       └────────────► chorale_id_id         │
                                                                                    │                       │
                                                                                    │                       │
                                                                                    │                       │
                                                                                    │                       │
                                                                                    │                       │
                                                                                    │                       │
                                                                                    │                       │
                                                                                    └───────────────────────┘

Here is the statement generating the view:


CREATE VIEW `attendances` AS SELECT
    `d`.`chorale_date_id` AS `date_id`,
    `d`.`chorale_date` AS `date`,
    `d`.`chorale_date_saison` AS `season_id`,
    `d`.`chorale_date_type` AS `date_type`,
    `d`.`chorale_date_chorale_id` AS `chorale_id`,
    `d`.`chorale_date_etat` AS `date_etat`,
    `p`.`chorale_presences_id` AS `absence_id`,
    `p`.`chorale_presences_date_id` AS `chorale_presences_date_id`,
    `p`.`chorale_presences_inscription_id` AS `chorale_presences_inscription_id`,
    `i`.`chorale_inscription_id` AS `chorale_inscription_id`,
    `p`.`chorale_presences_etat` AS `attendance`,
    `i`.`chorale_inscription_pupitre` AS `chorale_inscription_pupitre`,
    `p`.`chorale_presences_validation` AS `chorale_presences_validation`,
    CONCAT( `c`.`chorale_id_nom`, ', ', `c`.`chorale_id_prenom`) AS `fullname`
FROM
    (
        (
            (
                `chorale_dates` `d`
            LEFT JOIN `chorales_presences` `p` ON
                (
                    (
                        `p`.`chorale_presences_date_id` = `d`.`chorale_date_id`
                    )
                )
            )
        LEFT JOIN `chorale_inscription` `i` ON
            (
                (
                    `p`.`chorale_presences_inscription_id` = `i`.`chorale_inscription_id`
                )
            )
        )
    LEFT JOIN `chorale_id` `c` ON
        (
            (
                `c`.`chorale_id_id` = `i`.`chorale_id_id`
            )
        )
    )
ORDER BY d.season_id ASC, `d`.`chorale_date` ASC ;

Because it is a LEFT JOIN, I am expecting to have all season dates for all registered chorists, and either an empty "attendance" value if there is no recorded absence in the chorale_presence table, or the column value if there is a matching record in that table. All chorists should have 37 entries, whether there is an entry in chorale_presence or not.

Yet, what this view returns only contains rows having date and an absence recorded. So depending on the chorist, I get varying amount of entries.

What am I doing wrong?

CodePudding user response：

I am expecting to have all season dates for all registered chorists

This is the definition of a CROSS join between chorale_dates where you store the dates and chorale_inscriptio where you store the chorists' registrations:

CREATE VIEW attendances AS 
SELECT
  d.chorale_date_id AS date_id,
  d.chorale_date AS date,
  d.chorale_date_saison AS season_id,
  d.chorale_date_type AS date_type,
  d.chorale_date_chorale_id AS chorale_id,
  d.chorale_date_etat AS date_etat,
  p.chorale_presences_id AS absence_id,
  p.chorale_presences_date_id AS chorale_presences_date_id,
  p.chorale_presences_inscription_id AS chorale_presences_inscription_id,
  i.chorale_inscription_id AS chorale_inscription_id,
  p.chorale_presences_etat AS attendance,
  i.chorale_inscription_pupitre AS chorale_inscription_pupitre,
  p.chorale_presences_validation AS chorale_presences_validation,
  CONCAT(c.chorale_id_nom, ', ', c.chorale_id_prenom) AS fullname
FROM chorale_dates d CROSS JOIN chorale_inscription i
LEFT JOIN chorales_presences p ON p.chorale_presences_date_id = d.chorale_date_id AND p.chorale_presences_inscription_id = i.chorale_inscription_id
LEFT JOIN chorale_id c ON c.chorale_id_id = i.chorale_id_id
ORDER BY d.season_id ASC, d.chorale_date ASC;