mysql - 怎么找孙子？

我正在尝试查找特定 ID 的所有孙子，但我似乎无法正确连接。下面的代码有效，但我需要从产品表中获取与其关联的产品。

SELECT b.category_id, b.category_name, b.parent, a.parent
FROM categories AS a, categories AS b
WHERE a.category_id = b.parent AND a.parent = 119

当我尝试 JOIN 产品表时，我不断收到错误消息:

SELECT *
FROM products p
INNER JOIN products_categories pc
ON p.product_id = pc.product_id
INNER JOIN (
      SELECT b.category_id, b.category_name, b.parent, a.parent
      FROM categories AS a, categories AS b
      WHERE a.category_id = b.parent AND a.parent = 119
      )x

我想要的结果是:(注意:查看我的 SQL Fiddle 以在代码 View 中查看架构)

(76, 'BR134', 'LEA530664', 'ITEM1234', 1499.99, 'yes', 'This is a nice gun'),
(77, 'mZCXGT', 'LEA471061', 'qwer345', 89.99, 'yes', 'Testing!'),
(78, 'ert', 'LEA023991', 'asdf34', 129.99, 'yes', 'Another test to get this right!'),
(79, 'model test', 'LEA355935', 'item test', 119.99, 'yes', 'This is another test dammit!'),
(80, 'CZV1354', 'LEA741837', 'LI-1234', 1299.99, 'yes', 'This is a hipoint weapon!'),
(81, 'PINK12G', 'LEA008558', 'PINK-SHG', 89.99, 'yes', 'YEP! This is pink!'),
(82, 'BOWTECH', 'LEA762521', 'asdf', 899.99, 'yes', 'This is a test!'),
(83, 'LX32', 'LEA346903', 'MADEUP', 1499.99, 'yes', 'This is Carters gun.');

SQL fiddle :

http://sqlfiddle.com/#!2/dd66c/2

这是我的架构:

最佳答案

我认为您正在寻找这个(我为“可调试性”添加了类别名称):

SELECT
  p.product_id
, p.model
, p.sku
, p.item_number
, p.msrp
, p.availability
, p.description
, grand_child.category_name AS grand_child_category
, child.category_name AS child_category
, parent.category_name AS parent_category
FROM categories parent
INNER JOIN categories child
ON parent.category_id = child.parent
INNER JOIN categories grand_child
ON child.category_id = grand_child.parent
INNER JOIN products_categories pc
ON grand_child.category_id = pc.category_id
INNER JOIN products p
ON p.product_id = pc.product_id
WHERE parent.category_id = 119

关于mysql - 怎么找孙子？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14449549/