SQL Query to Handle Missing Phone Numbers: A Step-by-Step Solution

To answer this question, I will provide the code and output that solves the problem.

SELECT p.Person, COALESCE(e.Message, i.Message, 'No Match')
FROM 
  Person p
  LEFT JOIN ExternalNumber e ON p.Number = e.ExternalNumber
  LEFT JOIN InternalNumber i ON p.Number = i.InternalNumber

This SQL query will join the Person table with both the ExternalNumber and InternalNumber tables. It uses a LEFT JOIN, which means it will include all records from the Person table, even if there is no match in either the ExternalNumber or InternalNumber tables.

The COALESCE function is used to return the first non-null value in the list of arguments. In this case, it will be used to preferentially pick the external number over the internal one, and then ‘No Match’ if neither is available.

Last modified on 2024-04-22