选择一个MYSQL结果，其中表一和二表匹配，两个和三个不匹配

I have a two tables with users in an old format and a new format. I want to match the users with the old format to a separate table, then exclude all users who also show up in the new user format table. My data is like this:

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

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|
+----+----------+-------+----------+

I want to select my result by specifying: posts.id = 1 and posts.user_id = oldUsers.id and newUsers.oldid != oldUsers.id so that I only receive oldUser.id equaling 88 because he wasn't in the newUsers list.

我想通过指定来选择我的结果:posts.id = 1和posts.user_id = oldUsers.id和newUsers.oldid!= oldUsers.id以便我只接收oldUser.id等于88,因为他不在newUsers列表中。

I have tried all kinds of JOINS and SUBQUERIES. I keep getting all of the results and not the results minus corresponding entries in the newUsers table.

我尝试了各种JOINS和SUBQUERIES。我一直得到所有的结果,而不是结果减去newUsers表中的相应条目。

3 个解决方案

#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);

#2

Here is a way to do it

这是一种方法

select 
o.* from oldUsers o 
left join newUsers n on o.id = n.oldid 
left join posts p on  n.oldid = p.user_id or o.id = p.user_id 
where n.id is null and p.id= 1;

For better performance add the following indexes

为了获得更好的性能,请添加以下索

alter table newUsers add index oldid_idx(oldid);
alter table posts add index user_post_idx (id,user_id);

#3

I ended up finding my answer on my own and then came here to find others tried. Abhik's code did work, but was too inefficient to use. I ended up playing with my own code and IS NULL until I found something that was much more efficient.

我最终找到了自己的答案,然后来到这里寻找其他人的尝试。 Abhik的代码确实有效,但使用效率太低。我最终使用自己的代码和IS NULL玩,直到找到更高效的东西。

select o.* from posts p, oldUsers o 
LEFT JOIN newUsers n ON o.id = n.oldid 
WHERE p.user_id = o.id AND p.id = 1 AND n.id IS NULL

Executes in .0044 seconds. Something I can use on a production site.

执行时间为.0044秒。我可以在生产网站上使用的东西。

With indexes added from previous answer it now executes in .001x seconds so definately going with my own code.

使用从上一个答案添加的索引,它现在在.001x秒内执行,因此肯定使用我自己的代码。

#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);

#2