mysql - 如何从一个表中查询另一个表中不可用的数据，假设相同的数据也不应该在同一个表中？

Question

假设我有两个表，用户表和注册表。它们都包含“secret_token”列。我想根据 secret token 从注册表中获取所有结果:

1. 未在注册表中注册(针对此问题存在 tinyint 列“is_complete”)
2. 不得针对用户表中的任何用户出现。

演示:

用户

id name secret_token
1  jack 13drasdadad
2  john as5a6889sda

注册

id secret_token is_complete
1  13drasdadad  1           // note this exists in user table (shouldn't be in the result)
2  1agf803sdd2  0
3  gh994hkakll  0
4  gzgfg03zzd2  0
5  gh994hkakll  1           // note this token exists twice (where in one row is_complete is 1). Hence should be excluded from results.

我正在寻找的样本输出是:

id secret_token is_complete
2  1agf803sdd2  0
4  gzgfg03zzd2  0

到目前为止，我开发的查询是:

SELECT 
    t.*
FROM
    registration t
        INNER JOIN
    registration t1 on t1.id = t.id and t1.is_complete = false
        LEFT JOIN
    user u ON t.secret_token = u.secret_token
WHERE
    u.id IS NOT NULL
ORDER BY t.id DESC

附言通过加入寻找解决方案。子查询可以轻松解决这个问题。

Answer 1

要获得所需的结果，您可以使用以下查询，无需再次加入您的 registration 表，只需对用户执行 left join 并在 where 子句返回 u.id IS NULL

的记录

SELECT 
    t.*
FROM
    registration t
LEFT JOIN `user` u ON t.secret_token = u.secret_token
WHERE t.is_complete = 0 AND u.id IS NULL
ORDER BY t.id DESC

DEMO

编辑 相同的 secret token 不应该有 is_complete = 1 才能出现在结果集中

SELECT 
    t.*
FROM
    registration t
LEFT JOIN registration t1 ON(t.secret_token = t1.secret_token
                            AND t1.is_complete = 1
                            )
LEFT JOIN `user` u ON t.secret_token = u.secret_token 
 WHERE t1.secret_token IS NULL AND u.id IS NULL
ORDER BY t.id DESC

DEMO