困难的层次查询和连接

Question

（注：这里的数据是一个虚构的例子，因为我无法发布真实的数据。争论表结构没有意义或应该改变是没有用的）

数据库是Oracle 10g。

给定：

Products
------------
ID
Price

Sample Data:
ID Product_ID Customer Quantity
-------------------------
1  30          2        10
2  40          2        15
3  50          5        7
4  60          5        9


Product_types
-----------
ID
Name
Parent
Type

Data
ID Product_ID  Name        Parent    
----------------------------------
1  10          Box                    
2  20          Toolbox     10      
3  30          Hammer      20     
4  40          Nail        30 

查询：

select * from (select * from Product_types t
START WITH t.Parent = 20
CONNECT BY PRIOR t.Product_ID = t.PARENT) t_result
left join Products p on T_RESULT.Product_ID = P.Product_ID
where P.Customer = 2;

当前输出：

Product_ID  Name      Parent     Product_ID_1   Customer   Quantity
-------------------------------------------------------------------
30          Hammer    20         30             2          10
40          Nail      30         40             2          15

期望输出：

Product_ID  Name      Parent     Product_ID_1   Customer   Quantity
---------------------------------------------------------------------
20          Toolbox   10
30          Hammer    20         30               2          10
40          Nail      30         40               2          15

我意识到由于我的 where 子句，它只选择 customer = 2 的行。但是，我想知道是否有任何方法也可以到达层次结构的顶部。我的第一个想法是左连接仍应为工具箱行提供 NULL 客户，但该行不包括在内。我还尝试过完整的外部连接。

我怀疑我可能必须运行两个查询并手动组合结果才能获得我想要的结果，但想先咨询一些专家。

任何人都可以想出一种方法来获得所需的输出吗？

Answer 1

这有效吗？

select * from  
(select * from Product_types t
 START WITH T.Parent = 20 
 CONNECT BY PRIOR t.Product_ID = t.PARENT) t_result
left join Products p on T_RESULT.Product_ID = P.Product_ID
AND P.Customer = 2;

注意：我将最后一行的 WHERE 更改为 AND，以使条件成为外连接的一部分。

Answer 2

此外，您还应该更改递归的起点：

SELECT *
FROM (
  SELECT *
  FROM Product_types t
  START WITH t.Parent = 10 
  CONNECT BY PRIOR t.Product_ID = t.PARENT
) t_result
LEFT OUTER JOIN Products p 
  ON T_RESULT.Product_ID = P.Product_ID
  AND P.Customer = 2
;

或者甚至使用 START WITH t.Parent IS NULL 来获取完整的递归。