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

我有这张桌子：

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

我想从“收件箱”的“发件人编号”列中获取联系人、地区、分区或村庄的名称或spvname。我怎样才能做到这一点？结果可能是这样

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

提前谢谢，对不起我的英语不好。

最佳答案

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

关于mysql - 从多个表格的电话号码中获取姓名？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5705184/