Question 113534 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql join table and using not exist on same joined table

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks to @FanoFN, in the end, i sendup using NOT IN, which am not sure if that is the best way to go about doing this. I will not mark this as accepted answer in case if there is better way of doing it.

    SQL FIDDLE

    SET @me := 'p1';

SELECT tu.*, mt.m_stat, mt.m_from, mt.m_to, file.f_url
FROM TABLE_USERS tu

INNER JOIN TABLE_USER_FILES file 
ON file.f_uid =  tu.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_from = tu.uid
AND mt.m_stat = 1

WHERE tu.uid <> @me
AND tu.uid NOT IN (
   SELECT m_from 
   FROM TABLE_USER_MATCHES
   WHERE m_to = @me
)
AND tu.uid NOT IN (
   SELECT m_to
   FROM TABLE_USER_MATCHES
   WHERE m_from = @me
)

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search