JOINing 3 tables with ORDER, LIMIT and WHERE I am trying to join these 3 tables by key organization_id but I had no success.
What I'm trying to achieve: I know ipo and I want to get some values from name_entries and address_entries.
First query gives me organization_id, which is used in second and third query.
Is this even possible in 1 query?

My current solution is calling these queries separately and then joining them in my code.

SELECT * FROM "rpo"."organization_identifier_entries" WHERE ipo = '********' ORDER BY updated_at DESC LIMIT 1;
SELECT * FROM "rpo"."organization_address_entries" WHERE organization_id='########' ORDER BY updated_at DESC LIMIT 1;
SELECT * FROM "rpo"."organization_name_entries" WHERE organization_id='########'  ORDER BY effective_to DESC NULLS FIRST LIMIT 1;
Using LEFT JOIN LATERAL

I think what you want is something like,

SELECT *
FROM rpo.organization_identifier_entries
LEFT JOIN LATERAL (
  SELECT *
  FROM rpo.organization_address_entries
  ORDER BY updated_at DESC
  FETCH FIRST ROW ONLY
) AS oae
  USING (organization_id)
LEFT JOIN LATERAL (
  SELECT *
  FROM rpo.organization_name_entries
  ORDER BY effective_to DESC NULLS FIRST
  FETCH FIRST ROW ONLY
) AS one
  USING (organization_id)
WHERE ipo = '********'
FETCH FIRST ROW ONLY;


Oh, and btw, never double-quote things in PostgreSQL unless you want to enable a very stupid capability to make identifiers caps-sensitive. This is a bad idea, unless that third party is also doing it and they have capital letters in their identifiers.