'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