MySQL多表关联查询：排除指定创建者匹配记录及当前用户

原查询的核心问题 #

1. NOT EXISTS子查询未关联外部用户：原语句中NOT EXISTS (SELECT m_stat FROM TABLE_USER_MATCHES WHERE m_from = "p1")会检查系统中是否存在任何p1发起的匹配，而非当前用户是否被p1发起过匹配。只要p1有发起记录，整个条件就不成立，直接返回空结果。
2. INNER JOIN过滤了无gallery文件的用户：预期结果中的b1、h1、a1在TABLE_USER_FILES中没有对应记录，使用INNER JOIN会直接排除这些用户，与预期不符。
3. LEFT JOIN关联条件偏离需求：原LEFT JOIN的match.m_to = "p1"是关联p1作为接收方的匹配，既不符合“关联不由p1创建的匹配记录”的要求，也对最终筛选目标无帮助。

修正后的查询语句 #

如果需要同时获取用户信息、最新gallery文件（无则返回NULL）以及非p1创建的匹配状态，使用以下语句：

SELECT 
    user.uid, 
    user.u_name, 
    file.f_url,
    match.m_stat
FROM TABLE_USERS user
-- LEFT JOIN 保留无gallery文件的用户，同时获取每个用户的最新gallery文件
LEFT JOIN TABLE_USER_FILES file 
    ON file.f_uid = user.uid
    AND file.fid = (
        SELECT MAX(fid) 
        FROM TABLE_USER_FILES
        WHERE f_uid = user.uid
          AND f_path = 'gallery'
    )
-- LEFT JOIN 关联当前用户参与的、非p1创建的匹配记录
LEFT JOIN TABLE_USER_MATCHES match
    ON (match.m_from = user.uid OR match.m_to = user.uid)
    AND match.m_from != 'p1'
WHERE 
    user.uid != 'p1'
    -- 筛选未被p1发起过匹配的用户
    AND NOT EXISTS (
        SELECT 1 
        FROM TABLE_USER_MATCHES
        WHERE m_from = 'p1'
          AND m_to = user.uid
    )
LIMIT 0, 20;

如果只需要用户的基础信息（与预期结果完全一致），可以简化为：

SELECT 
    user.uid, 
    user.u_name
FROM TABLE_USERS user
WHERE 
    user.uid != 'p1'
    AND NOT EXISTS (
        SELECT 1 
        FROM TABLE_USER_MATCHES
        WHERE m_from = 'p1'
          AND m_to = user.uid
    )
LIMIT 0, 20;

关键修正说明 #

• 替换INNER JOIN为LEFT JOIN：确保没有gallery文件的用户不会被过滤，符合预期结果要求。
• 修正NOT EXISTS子查询：添加m_to = user.uid关联外部用户，精准检查当前用户是否被p1发起过匹配，仅保留未被p1发起匹配的用户。
• 调整文件查询的关联逻辑：将子查询中的f_uid = file.f_uid改为f_uid = user.uid，确保子查询针对当前用户筛选最新gallery文件，逻辑更准确。

内容的提问来源于stack exchange，提问作者Peter