mysql - 从节点父亲获取值时出错？

Question

我有一个示例数据:

product(id, parent_id, name)
        1 |  0  | Windows
        2 |  1  | XP
        3 |  1  | 7
manufacturer(id, parent_id, name)
        1 |  0  | Westwood Studios
        2 |  1  | Red Alert 1
        3 |  1  | Red Alert 2
product_manufacturer(product_id, manufacturer_id)
        2 |  2  
        3 |  2
        3 |  3

和mysql:

SELECT prod.name
FROM `manufacturer` AS child
INNER JOIN `manufacturer` AS parent ON parent.id=child.parent_id 
INNER JOIN `product_manufacturer` AS pr_ma ON pr_ma.manufacturer_id=child.manufacturer_id
INNER JOIN `product` AS prod ON pr_ma.product_id=prod.product_id 
WHERE parent.id=1
GROUP BY prod.id

当 parent.id=1 (manufacturer=Westwood Studios) 结果为 XP, 7

当 parent.id=1 结果为 Windows 时如何修复

Answer 1

SELECT prodparent.name
FROM `manufacturer` AS child
INNER JOIN `manufacturer` AS parent ON parent.id=child.parent_id 
INNER JOIN `product_manufacturer` AS pr_ma ON pr_ma.manufacturer_id=child.manufacturer_id
INNER JOIN `product` AS prod ON pr_ma.product_id=prod.product_id 
INNER JOIN product AS prodparent ON prod.parent_id=prodparent.id
WHERE parent.id=1
GROUP BY prod.id