PostgreSQL INNER JOIN With Most Current Records

Question

I am trying to create this VIEW:

CREATE VIEW highlight_on_rss_feed AS
 
 SELECT highlight_on_article.title AS title,
    highlight_on_meta_description.meta_description AS description,
    concat('https://rons-home.net/en/highlight-on/article/', highlight_on_profile.reference::text ) AS link,
    highlight_on_profile.submit_date AS pubdate
   FROM highlight_on_profile
     JOIN highlight_on_article ON highlight_on_profile.reference = highlight_on_article.highlight_on_profile_reference
     JOIN highlight_on_meta_description ON highlight_on_meta_description.highlight_on_profile_reference = highlight_on_profile.reference
  ORDER BY highlight_on_profile.submit_date DESC
 LIMIT 15;

For the the 2 INNER JOIN I need the most recent records from highlight_on_meta_description.highlight_on_profile_reference and highlight_on_article.highlight_on_profile_reference

I've created these 2 tables so I may look back at what the previous content was.

TABLE: highlight_on_profile

reference bigint ( nextval('highlight_on_profile_reference_seq'::regclass) )
submit_date timestamp with time zone
submit_membership_reference bigint
approve_date timestamp with time zone
approve_membership_reference bigint
approve_note text

TABLE highlight_on_article

reference bigint ( nextval('highlight_on_article_reference_seq'::regclass) )
highlight_on_profile_reference bigint
title text
author text
article text
sitemap_include_date timestamp with time zone

TABLE: highlight_on_meta_description

reference bigint ( nextval('highlight_on_meta_description_reference_seq'::regclass) )
highlight_on_profile_reference bigint
meta_description text
effective_date timestamp with time zone
membership_reference bigint
sitemap_include_date timestamp with time zone

Answer 1

The solution is to use LATERAL.

SELECT article.title AS title,
   meta_description.meta_description AS meta_description,
   concat('https://rons-home.net/en/highlight-on/article/', profile.reference::text ) AS link,
   profile.submit_date AS pubdate
  FROM highlight_on_profile profile ,
    lateral (
        SELECT * FROM highlight_on_article article
        WHERE article.highlight_on_profile_reference = profile.reference
        ORDER BY article.reference DESC LIMIT 1
    ) AS article ,
    lateral (
        SELECT * FROM highlight_on_meta_description meta_description
        WHERE meta_description.highlight_on_profile_reference = profile.reference
        ORDER BY meta_description.reference DESC LIMIT 1
    ) AS meta_description 
 ORDER BY profile.submit_date DESC
LIMIT 15;

The database user also needs permission

GRANT SELECT ON TABLE public.highlight_on_rss_feed TO user;