'DISTINCT ON with a One to Many Relation through another table
I have four Postgres tables:
Professions
| id | name |
|---|---|
| 123 | profession 1 |
| 345 | profession 2 |
Versions
| id | professionId | status | createdAt |
|---|---|---|---|
| 567 | 123 | Live | 2020-01-01 |
| 999 | 123 | Draft | 2021-02-01 |
| 666 | 345 | Live | 2021-01-01 |
Organisations
| id | name |
|---|---|
| 333 | organisation 1 |
| 444 | organisation 2 |
| 655 | organisation 3 |
ProfessionToOrganisation
| id | professionId | organisationId | Role |
|---|---|---|---|
| 665 | 123 | 333 | Regulator |
| 533 | 123 | 444 | Something else |
| 534 | 345 | 444 | Regulator |
I am trying to get the latest version of each profession, regardless of their status, so am using a combination of DISTINCT ON and ORDER BY like so:
SELECT DISTINCT ON (
professionVersions.professionId,
professions.name
) professionVersions.id AS "professionVersions_id", professions.name, organisations.name
FROM professionVersions
LEFT JOIN professions ON professions.id = professionVersions.professionId
LEFT JOIN professionToOrganisation ON
professionToOrganisation.professionId = professions.id
LEFT JOIN organisations ON professionToOrganisation.organisationId = organisations.id
ORDER BY
professions.name,
professionVersions.professionId,
professionVersions.created_at DESC
However, I only get one organisation returned for each profession, i.e:
| professionVersions_id | name | name |
|---|---|---|
| 999 | Profession 1 | Organisation 1 |
| 666 | Profession 2 | Organisation 2 |
Whereas I wouild like:
| professionVersions_id | name | name |
|---|---|---|
| 999 | Profession 1 | Organisation 1 |
| 999 | Profession 1 | Organisation 2 |
| 666 | Profession 2 | Organisation 2 |
How do I go about achieving this?
I've got a DB Fiddle here - https://www.db-fiddle.com/f/44iWbw7oyDtVMsQfE45jPA/0
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
