By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TABLE_USERS (
uid VARCHAR(20),
u_name VARCHAR(255));
INSERT INTO TABLE_USERS VALUES
('p1','Peter'),
('j1','John'),
('f1','Foo'),
('b1','Bar'),
('h1','Hana'),
('a1','Ada');
CREATE TABLE TABLE_USER_MATCHES (
mid VARCHAR(20),
m_from VARCHAR(20),
m_to VARCHAR(20),
m_stat INT);
INSERT INTO TABLE_USER_MATCHES VALUES
('mp1','p1','j1',1),
('mp2','p1','f1',1),
('mp3','a1','p1',1);
CREATE TABLE TABLE_USER_FILES (
fid VARCHAR(20),
f_uid VARCHAR(20),
f_url VARCHAR(20),
f_path VARCHAR(20));
INSERT INTO TABLE_USER_FILES VALUES
('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');
Records: 6 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
Records: 5 Duplicates: 0 Warnings: 0
SELECT users.*, t.m_stat, t.f_url
FROM TABLE_USERS users
LEFT JOIN
(SELECT tu.uid, tm.m_stat, tf.f_url
FROM TABLE_USERS tu
INNER JOIN TABLE_USER_FILES tf
ON tf.f_uid = tu.uid
AND tf.fid = (
SELECT MAX(fid) FROM TABLE_USER_FILES
WHERE f_uid = tf.f_uid
AND f_path = "gallery"
)
INNER JOIN TABLE_USER_MATCHES tm
ON tu.uid=(CASE WHEN tu.uid != tm.m_from THEN tm.m_to ELSE tm.m_from END)
) t
ON users.uid = t.uid
WHERE t.m_stat IS NULL
| uid | u_name | m_stat | f_url |
|---|---|---|---|
| b1 | Bar | null | null |
| h1 | Hana | null | null |
| a1 | Ada | null | null |