MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TABLE_USERS(
   uid    VARCHAR(4) NOT NULL
  ,u_name VARCHAR(9) NOT NULL
);
INSERT INTO TABLE_USERS(uid,u_name) VALUES ('p1','Peter');
INSERT INTO TABLE_USERS(uid,u_name) VALUES ('j1','John');
INSERT INTO TABLE_USERS(uid,u_name) VALUES ('f1','Foo');
INSERT INTO TABLE_USERS(uid,u_name) VALUES ('b1','Bar');
CREATE TABLE TABLE_USER_POST(
   pid    VARCHAR(4) NOT NULL
  ,p_name VARCHAR(9) NOT NULL
  ,p_uid  VARCHAR(8) NOT NULL
);
INSERT INTO TABLE_USER_POST(pid,p_name,p_uid) VALUES ('xp1','PHP','p1');
INSERT INTO TABLE_USER_POST(pid,p_name,p_uid) VALUES ('xp2','SQL','p1');
INSERT INTO TABLE_USER_POST(pid,p_name,p_uid) VALUES ('xp3','JS','j1');
INSERT INTO TABLE_USER_POST(pid,p_name,p_uid) VALUES ('xp4','CSS','b1');
CREATE TABLE TABLE_USER_FILES(
   fid    VARCHAR(4) NOT NULL
  ,f_uid  VARCHAR(8) NOT NULL
  ,f_url  VARCHAR(9) NOT NULL
  ,f_path VARCHAR(10) NOT NULL
);
INSERT INTO TABLE_USER_FILES(fid,f_uid,f_url,f_path) VALUES ('fa1','p1','ax.png','gallery');
INSERT INTO TABLE_USER_FILES(fid,f_uid,f_url,f_path) VALUES ('fb2','p1','bc.png','gallery');
INSERT INTO TABLE_USER_FILES(fid,f_uid,f_url,f_path) VALUES ('bc3','j1','cc.png','gallery');
INSERT INTO TABLE_USER_FILES(fid,f_uid,f_url,f_path) VALUES ('fd4','f1','cx.png','gallery');
INSERT INTO TABLE_USER_FILES(fid,f_uid,f_url,f_path) VALUES ('fe5','j1','qd.png','gallery');

select * from TABLE_USERS;
select * from TABLE_USER_POST;
select * from TABLE_USER_FILES;


uid
u_name


p1
Peter

j1
John

f1
Foo

b1
Bar




pid
p_name
p_uid


xp1
PHP
p1

xp2
SQL
p1

xp3
JS
j1

xp4
CSS
b1




fid
f_uid
f_url
f_path


fa1
p1
ax.png
gallery

fb2
p1
bc.png
gallery

bc3
j1
cc.png
gallery

fd4
f1
cx.png
gallery

fe5
j1
qd.png
gallery


SELECT post.*, user.u_name, files.f_url
FROM TABLE_USER_POST post

INNER JOIN TABLE_USERS user
ON user.uid = post.p_uid

LEFT OUTER JOIN LATERAL (
    SELECT f_url, f_uid
        FROM TABLE_USER_FILES tuf
        WHERE f_path = "gallery"
          AND tuf.f_uid = post.p_uid
        ORDER BY fid DESC
    LIMIT 1
) files ON true

ORDER BY post.pid DESC 
LIMIT 0, 20


pid
p_name
p_uid
u_name
f_url


xp4
CSS
b1
Bar
null

xp3
JS
j1
John
qd.png

xp2
SQL
p1
Peter
bc.png

xp1
PHP
p1
Peter
bc.png


SELECT post.*, user.u_name,
  ( SELECT f_url
        FROM TABLE_USER_FILES tuf
        WHERE f_path = "gallery"
          AND tuf.f_uid = post.p_uid
        ORDER BY fid DESC
    LIMIT 1
  ) AS f_url
FROM TABLE_USER_POST post

INNER JOIN TABLE_USERS user
ON user.uid = post.p_uid

ORDER BY post.pid DESC 
LIMIT 0, 20


pid
p_name
p_uid
u_name
f_url


xp4
CSS
b1
Bar
null

xp3
JS
j1
John
qd.png

xp2
SQL
p1
Peter
bc.png

xp1
PHP
p1
Peter
bc.png

fid	f_uid	f_url	f_path
fa1	p1	ax.png	gallery
fb2	p1	bc.png	gallery
bc3	j1	cc.png	gallery
fd4	f1	cx.png	gallery
fe5	j1	qd.png	gallery

pid	p_name	p_uid	u_name	f_url
xp4	CSS	b1	Bar	null
xp3	JS	j1	John	qd.png
xp2	SQL	p1	Peter	bc.png
xp1	PHP	p1	Peter	bc.png