CODEWITHSUNDEEP
sqlplsqloracle10g
wadesworld
wadesworld

Reputation: 13763

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 wonder 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?

Upvotes: 1

Views: 7405

Answers (2)

bw_üezi
bw_üezi

Reputation: 4574

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.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132680

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.

Upvotes: 3

Related Questions