mysql - 如何从具有多个where条件的两个表中获取记录

Question

我有两个表，一个是 Userregistration，第二个是 user_verificationcode，我只需要从中获取那些电子邮件和移动状态为 1 的记录。下面是我的表结构

用户注册表

........................................
id      fullname   mobile_no       email
.........................................
5      varun         12344567   abc@gmail
6      nitin         12345678   def@gmail

用户验证码

.............................................
id      user_id     codetype      status
............................................
1       5            email           0  
2       5            mobile          1
3       6            email           1  
4       6            mobile          1

我想要这样的输出

........................................
id      fullname   mobile_no       email
.........................................
6       nitin         12345678   def@gmail

为此，我使用了下面的查询，但它不起作用，我不知道如何实现这一点。

SELECT * FROM Userregistration 
INNER JOIN user_verificationcode ON Userregistration.`id`=user_verificationcode.`user_id` 
where user_verificationcode.`codetype`='email' 
and user_verificationcode.`status`='1' 
and user_verificationcode.`codetype`='mobile' 
and user_verificationcode.`status`='1'

Answer 1

SELECT r.* 
FROM Userregistration r
INNER JOIN user_verificationcode ve ON r.id = ve.user_id 
                                  and ve.codetype = 'email' 
                                  and ve.status = 1 
INNER JOIN user_verificationcode vm ON r.id = vm.user_id
                                  and  vm.codetype = 'mobile' 
                                  and vm.status = 1 

或

SELECT * 
FROM Userregistration
where id in
(
    select user_id
    from user_verificationcode 
    group by user_id
    having sum(codetype = 'email' and status = 1) > 0
       and sum(codetype = 'mobile' and status = 1) > 0
)