MySQL多态关联查询问题：关联失效与动态查询实现

问题1：解决关联查询的1066表别名重复错误 #

你的SQL报错原因是重复引用了同一个tblstall表且未做区分，数据库无法识别两个相同的表引用。正确的做法是关联对应的动物表（而非再次关联tblstall），同时建议加上animalType过滤以符合多态关联逻辑。

修正后的SQL示例（关联tbldogs）：

SELECT * 
FROM tblstall 
INNER JOIN tbldogs ON tblstall.animal = tbldogs.ID
WHERE tblstall.animalType = 2;

如果需要同时关联多个动物表，可给表指定别名避免冲突：

SELECT s.*, d.name AS dog_name, h.name AS horse_name
FROM tblstall s
LEFT JOIN tbldogs d ON s.animal = d.ID AND s.animalType = 2
LEFT JOIN tblHorses h ON s.animal = h.ID AND s.animalType = 1;

问题2：实现基于animalType的动态关联查询 #

根据animalType的值动态关联不同表，有两种实用方案：

方案1：LEFT JOIN + 字段合并 #

通过左连接两个动物表，结合animalType匹配条件，再用COALESCE统一提取不同表的字段值，适合表结构相似的场景：

SELECT 
    s.*,
    COALESCE(d.name, h.name) AS animal_name,
    COALESCE(d.breed, h.breed) AS animal_breed
FROM tblstall s
LEFT JOIN tbldogs d ON s.animal = d.ID AND s.animalType = 2
LEFT JOIN tblHorses h ON s.animal = h.ID AND s.animalType = 1
WHERE d.ID IS NOT NULL OR h.ID IS NOT NULL;

方案2：UNION ALL合并查询结果 #

将两种类型的查询分开编写后合并，适合表字段差异较大的场景：

-- 查询关联狗的记录
SELECT s.*, d.name AS animal_name, d.breed AS animal_breed
FROM tblstall s
INNER JOIN tbldogs d ON s.animal = d.ID
WHERE s.animalType = 2

UNION ALL

-- 查询关联马的记录
SELECT s.*, h.name AS animal_name, h.breed AS animal_breed
FROM tblstall s
INNER JOIN tblHorses h ON s.animal = h.ID
WHERE s.animalType = 1;

内容的提问来源于stack exchange，提问作者Leon