MySQL join two table an filter data

Question

I have two table

First Table : tblUser

userID  name    phone           email
1       A       1234567890      [email protected]
2       B       1234578569      [email protected]
3       C       1234567891      [email protected]
4       D       5456987452      [email protected]
5       E       1456987452      [email protected]

Second Table : tblShareLocation

userID  shareUserid shareLocation
1           2           0
2           3           1
1           3           1

I have to check how many user are registered(for this i have check list of mobile number into tblUser table and if number are exist which means this user are registerd) and check how many registerd users are shared their location with userID=1

For example if I input userID=1 and mobile number ('1234578569','1234567891','5456987452','1234567856')

Then the output are below
shareUserid     name    phone       email           shareLocation
2               B       1234578569  [email protected]         0
3               C       1234567891  [email protected]         1
4               D       5456987452  [email protected]         0

I want the above output because

1. For input, I give 4 different numbers, out of 4 numbers three are registered so these three numbers with their details are shown on output
2. userID=1 shared their location to userID=3 thats why shareLocation=1

I am using MySQL.

Answer 1

It seems like a simple LEFT JOIN operation is what you need:

SELECT u.userID AS shareUserID, u.name, u.phone, u.email, 
       COALESCE(sl.shareLocation, 0) AS shareLocation
FROM tblUser AS u
LEFT JOIN tblShareLocation AS sl ON u.userID = sl.shareUserID AND sl.userID = 1
WHERE phone IN ('1234578569','1234567891','5456987452','1234567856')

Demo here