varun joshi varun joshi - 6 months ago 9
SQL Question

How to get records from two tables with multiple where conditions

I have a two tables one is Userregistration and second is user_verificationcode from which i have to get only those record whose email and mobile status are 1.Below are my table structure

Userregistration table

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


user_verificationcode

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


I want this kind of output

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


For this i have used below query but its not working i am not getting how to achieve this.

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

You probably want to return only Userregistration fields, since you already know the info contained in user_verificationcode table. In this case you can use the following query:

SELECT t1.* 
FROM Userregistration AS t1
JOIN ( 
   SELECT user_id
   FROM user_verificationcode 
   WHERE codetype IN ('mobile', 'email')
   GROUP BY user_id
   HAVING COUNT(DISTINCT codetype) = 2 AND SUM(status <> 1) = 0 
) AS t2 ON t1.id = t2.user_id