Difficult hierarchical query and join
(Note: the data here is a made up example since I can't post the real data. No use arguing that the table structure doesn't make sense or should change)
Database is Oracle 10g.
Given: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
Query:
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;
Current Output:
Product_ID Name Parent Product_ID_1 Customer Quantity
-------------------------------------------------------------------
30 Hammer 20 30 2 10
40 Nail 30 40 2 15
Desired Output:
Product_ID Name Parent Product_ID_1 Customer Quantity
---------------------------------------------------------------------
20 Toolbox 10
30 Hammer 20 30 2 10
40 Nail 30 40 2 15
I realize it's only selecting the rows with customer = 2 because of my where clause. However, I won开发者_如何学Goder if there's any way to get the top of the hierarchy as well. My first thought was a left join should still provide the Toolbox row with a NULL customer, but the row is not included. I've also tried a full outer join.
I suspect I may be in the realm of having to run two queries and combine the results manually to get my desired result, but wanted to consult some experts first.
Can anyone come up with a way to get the desired output?
Does this work?
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;
Note: I changed WHERE to AND on the last line to make the condition part of the outer join.
Also you should change the start point of the recursion:
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
;
Or even use START WITH t.Parent IS NULL
to get the full recursion.
精彩评论