mysql - 根据结果​​递归查询和更改目标表

Question

假设我有这样的查询:

SELECT type_person FROM tbl_event WHERE id_animal = 1;

这给了我人的类型和要从其他表加入的目标表(在这种情况下，这将返回“worker”)以获取事件的全部数据，现在要加入，我知道我必须做一个加入“ worker 表”，例如:

SELECT  a.*,b.*,z.*
FROM    tbl_event a
        left JOIN tbl_worker b
            ON a.type_person = b.type_person
        left JOIN tbl_animal z
            ON   z.id_animal = a.id_animal 
        WHERE z.id_animal = 1;

有没有办法通过类似的方式更改 JOIN tbl_worker b 左边的部分:

SELECT  a.*,b.*,z.*
FROM    tbl_event a
        left JOIN (SELECT type_person FROM tbl_event WHERE id_animal = 1)
            ON a.type_person = b.type_person
        left JOIN tbl_animal z
            ON   z.id_animal = a.id_animal 
        WHERE z.id_animal = 1;

here is a fiddle

Answer 1

当然，您需要执行以下操作:

SELECT  a.*,b.*,z.*
FROM    tbl_event a
        left JOIN (SELECT type_person FROM tbl_event WHERE id_animal = 1) as b
            ON a.type_person = b.type_person
        left JOIN tbl_animal z
            ON   z.id_animal = a.id_animal 
        WHERE z.id_animal = 1;

基本上您缺少别名(在本例中为 b)

Here's an updated fiddle