MySQL根据表的某个字段从不同的表进行查询

Question

我觉得这个标题有点含糊。下面我会解释一下。所以我有这些表

appointments table

id  created_by  created_by_id
---------------------------
1   provider        10
2   customer        5
3   admin           1

providers table

id first_name last_name
------------------------
..    ...       ...
10     x         y

customers table

id  first_name  last_name
-------------------------
..     ...       ...
5       a         b

admins table

id  first_name  last_name
--------------------------
..    ...          ...
1      c            d

我想根据created_by字段查询并返回相关帐户的全名，因此如果是provider，则查询provider表中的名称，依此类推。

我已经尝试过了

select  *,
        (
            SELECT 
                CASE 
                    WHEN appointments.created_by='provider' THEN 
                        CONCAT(d.first_name, " ", d.last_name)
                    WHEN appointments.created_by='customer' THEN 
                        CONCAT(p.first_name, " ", p.last_name)
                    ELSE CONCAT(p.first_name, " ", p.last_name)
                END
            FROM providers as d, customers as p
            WHERE 
                appointments.created_by_id = (CASE 
                    WHEN appointments.created_by='provider' THEN 
                        d.id
                    WHEN appointments.created_by='customer' THEN 
                        p.id
                    ELSE p.id
                END)
        )
from appointments

但是好像不行。你能帮我实现这样的表格吗

id  created_by  name
-------------------
1    provider   x y
2    customer   a b
3     admin     c d

Answer 1

select *,
    case appointments.created_by 
        when 'admin' then (select concat(first_name, ' ', last_name)  from admins where admins.id = appointments.created_by_id)
        when 'customer' then (select concat(first_name, ' ', last_name)  from customers where customers.id=appointments.created_by_id)
        when 'provider' then (select concat(first_name, ' ', last_name)  from providers where providers.id = appointments.created_by_id)
    END as name
from appointments

这是一个使用 case 方法的 sqlfiddle:http://sqlfiddle.com/#!9/43510/1