mysql - 选择表一和表二匹配而表二和表三不匹配的 MYSQL 结果

Question

我有两个表，用户分别采用旧格式和新格式。我想将旧格式的用户匹配到单独的表，然后排除也出现在新用户格式表中的所有用户。我的数据是这样的:

Table newUsers:
+----+-------+-------+----------+
| id | oldid | first | last     |
+----+-------+-------+----------+
|  1 |    10 | John  | Kennedy  |
|  2 |    66 | Mitch | Kupchak  |
+----+-------+-------+----------+

Table posts:
+----+---------+
| id | user_id |
+----+---------+
|  1 |      10 |
|  1 |      66 |
|  1 |      88 | 
|  2 |      88 | 
|  2 |      28 | 
|  3 |      10 | 
+----+---------+

Table oldUsers:
+----+----------+-------+----------+
| id | username | first | last     |
+----+----------+-------+----------+
| 10 |        A | John  | Kennedy  |
| 66 |        B | Mitch | Kupchak  |
| 88 |        C | Dale  | Earnhardt|
+----+----------+-------+----------+

Result wantend:
+----+----------+-------+----------+
| id | username | first | last     |
+----+----------+-------+----------+
| 88 |        C | Dale  | Earnhardt|
+----+----------+-------+----------+

我想通过指定来选择我的结果: posts.id = 1 和 posts.user_id = oldUsers.id 和 newUsers.oldid != oldUsers.id 这样我只收到等于 88 的 oldUser.id 因为他不在新用户列表。

我尝试过各种连接和子查询。我不断获得所有结果，而不是减去 newUsers 表中相应条目的结果。

Answer 1

select * from oldusers where id in

select * from

(select id from oldusers where id in
 select distinct userid from posts where id=1) 
 where id not in (select oldid from newusers);