从多个表中的电话号码获取姓名？

Question

我有这个表：

inbox table 
SenderNumber Message 

contact table 
Name Number 

district table 
id SpvName Number 

sub_district table 
id district_id SpvName Number 

village table 
id sub_district_id SpvName Number 

我想从接触，区，sub_district或村庄，从收件箱中的SenderNumber列基于名称或spvname。我如何实现这一目标？结果这

SenderNumber | Name | Type   | Message 
-------------+------+--------------+------------ 
123   | john | contact  | bla bla 
234   | mary | district spv | bla bla bla 

在此先感谢和抱歉我的英文不好，也许等。

Answer 1

SELECT i.SenderNumber, 
     COALESCE(c.Name, d.SpvName, sd.SpvName, v.SpvName) as Name, 
     CASE WHEN c.Name IS NOT NULL THEN 'contact' 
      WHEN d.SpvName IS NOT NULL THEN 'district' 
      WHEN sd.SpvName IS NOT NULL THEN 'sub_district' 
      WHEN v.SpvName IS NOT NULL THEN 'village' 
      ELSE '' 
     END AS Type, 
     i.Message 
    FROM inbox i 
     LEFT JOIN contact c 
      ON i.SenderNumber = c.Number 
     LEFT JOIN district d 
      ON i.SenderNumber = d.Number 
     LEFT JOIN sub_district sd 
      ON i.SenderNumber = sd.Number 
     LEFT JOIN village v 
      ON i.SenderNumber = v.Number 

Answer 2

像这样的工作，我相信......

SELECT i.SenderNumber, a.Name, a.Type, i.Message FROM 
inbox i 
INNER JOIN 
(SELECT SpvName as Name, 'contact' as Type, Number FROM contact 
UNION 
SELECT SpvName as Name, 'sub district' as Type, Number FROM sub_district 
UNION 
SELECT SpvName as Name, 'district' as Type, Number FROM district 
UNION 
SELECT SpvName as Name, 'village' as Type, Number FROM village 
) a 
ON i.SenderNumber = a.Number