How to identify a SMS subscriber in mobile connect by a record in Sales Cloud?

Question

As I understood, a subscriber will be added to mobile connect once the SMS was sent to the mobile number of that SFDC record. I tried to query the data to have a better understanding

SELECT
SubscriberKey,
MobileNumber,
OptOutStatusID,
OptOutDate,
OptInStatusID,
OptInDate
FROM
_SMSSubscriptionLog

I have some questions:

• Sometimes there are records with SubscriberKey like these: "Multiple subscriber keys found for this mobile number", "No MobileAddress record has been found or this mobile number". I also tried to find some records in all subscribers list but they can be found in nowhere.
  How do I identify if a record in mobile connect belongs to a record in SFDC? There are many records having the same Mobile Number also.
  
• In a record if the OptOutStatusID was 10 and OptInStatusID was 12, what does this mean? (document) Did that subscriber already unsubscribe now?

---

Updated: After researching, I found this way. In case I assume that those SMS subscribers only came from Contact in SFDC, I would do this SQL:

SELECT DISTINCT
a._ContactID as contactid,
b.SubscriberKey as subscriberkey,
a._MobileNumber as mobilenumber,
CASE
    WHEN a._Status = 1 THEN 'Active'
    WHEN a._Status = 2 THEN 'Inactive'
    ELSE 'Deleted'
END as status,
a._CountryCode as countrycode
FROM
_MobileAddress as a
INNER JOIN
_smsmessagetracking as b
ON a._ContactID = b.SubscriberID
WHERE b.SubscriberKey IN
(SELECT _ContactKey FROM ent.[Contact_Salesforce])

Since there were many duplicate results so DISTINCT statement would help to get rid them. 2 data views _MobileAddress and _smsmessagetracking were linked through "contact id" then we can get the subscriber key/contact key to identify them if they were records from our Contact ( the ent.[Contact_Salesforce] is my synchronized contact data extension).

Answer 1

Thanks for your update. My finding:

• The text in _SMSSubscriptionLog's SubscriberKey field: "Multiple subscriber keys found for this mobile number" is actually wrong, what it should be is "Multiple CONTACT keys found for this mobile number"
• "No MobileAddress record has been found or this mobile number" means that there used to be mobileconnect contacts and potentially sms outbounds associated to that mobile number but the contact was deleted.