Mysql join table and using not exist on same joined table

user3Ayy 0
Viewed 57

Am trying to select join record from TABLE_USER_MATCHES if the uid, is not the one that created the match m_from.

My intention is to join records from TABLE_USER_MATCHES if it exist and it's not created by m_from p1 and also ignore selecting p1 from TABLE_USERS as the current user querying database.

TABLE_USERS

uid | u_name  
----|---------
p1  | Peter 
j1  | John
f1  | Foo
b1  | Bar
h1  | Hana
a1  | Ada

TABLE_USER_MATCHES

mid | m_from  | m_to  | m_stat
----|---------|-------|---------
mp1 | p1      | j1    | 1
mp2 | p1      | f1    | 1
mp3 | a1      | p1    | 1

TABLE_USER_FILES

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

Query

SELECT user.*, match.m_stat, 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 match
ON match.m_to = "p1"
AND match.m_stat = 1

WHERE user.uid <> "p1"
AND NOT EXISTS (
    SELECT m_stat 
    FROM TABLE_USER_MATCHES
    WHERE m_from = "p1"
)

LIMIT 0, 20

Expected Result

b1  | Bar
h1  | Hana
a1  | Ada
left-join inner-join mysql
1 Answers

After much discussion in the comment section (until SO suggests to go chat ), perhaps this is what you're looking for:

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;

I've retained the most part of the first INNER JOIN and taking "select all none existing records in match or record other user created for me, but not the one I created for other users" into consideration I've added:

...
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)
...

I'm not sure if I got the correct concept yet so I hope OP will comment if anything I've missed.

Demo fiddle

FanoFN0
Related