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));
/*
Return all user except my profile [p1]
*/
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_type VARCHAR(20),
m_stat INT);
/*
p1 to all user don't show
all user to p1 show it
*/
INSERT INTO TABLE_USER_MATCHES VALUES
('mp1','p1','j1', 'like', 1),
('mp2','p1','f1', 'superlike', 1),
('mp3','a1','p1', 'like', 1);
CREATE TABLE TABLE_USER_FILES (
fid VARCHAR(20),
f_uid VARCHAR(20),
f_url VARCHAR(20),
f_path VARCHAR(20));
Records: 6 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
Records: 9 Duplicates: 0 Warnings: 0
SET @me := 'p1';
SELECT user.*, mt.m_stat, mt.m_type, mt.m_from, mt.m_to, file.f_url
FROM TABLE_USERS user
INNER JOIN TABLE_USER_FILES file
ON file.f_uid = user.uid
AND file.fid = (
SELECT MAX(fid) FROM TABLE_USER_FILES
WHERE f_uid = file.f_uid
AND f_path = "gallery"
)
LEFT JOIN TABLE_USER_MATCHES mt
ON mt.m_to = @me
AND mt.m_stat = 1
WHERE user.uid <> @me
AND user.uid NOT IN (
SELECT m_from
FROM TABLE_USER_MATCHES
WHERE m_to = @me
AND m_type = "dislike"
)
AND user.uid NOT IN (
SELECT m_to
FROM TABLE_USER_MATCHES
WHERE m_from = @me
)
| uid | u_name | m_stat | m_type | m_from | m_to | f_url |
|---|---|---|---|---|---|---|
| b1 | Bar | 1 | like | a1 | p1 | qx.png |
| a1 | Ada | 1 | like | a1 | p1 | kl.png |
| h1 | Hana | 1 | like | a1 | p1 | br.png |