CODEWITHSUNDEEP
oracle-databaseoracle12chierarchical-query
sam
sam

Reputation: 2616

Rebuild oracle hierarchical query from two tables

I am working on oracle hierarchical query for the below table Classification_Product and Order_details tables

Classification Product has the Classification_id which will be stored in the Order_detailsaccording to user selection

Classification Product table Classification Product table

Order_details table Order_details table

I want to select all products from Order_details table that has a parent LED Screen which will return all product that has parent LED Screen no matter what the child is 32inch or 50 inch or sony samsung etc etc

I tried to use below query but its replate rows a lot

           SELECT B.CLASSIFICATION_ID, LEVEL AS VLEVEL, A.CATEGORY_ID, A.CATEGORY_DESC, CONNECT_BY_ISLEAF AS leaf
             FROM PRODUCT_CLASSIFICATION A, ORDER_DETAILS B
            WHERE A.STATUS = 1 and b.created_on like sysdate--AND leaf =1 
       START WITH A.CATEGORY_ID IS NULL
       CONNECT BY A.CATEGORY_ID = PRIOR A.CLASSIFICATION_ID
ORDER SIBLINGS BY A.CLASSIFICATION_ID;

Upvotes: 0

Views: 52

Answers (1)

B Samedi
B Samedi

Reputation: 380

select od.*
from order_details od
where
  od.classification_id in (
    select p.classification_id
    from product p
    start with p.category_desc = 'LED Screen'
    connect by prior p.classification_id = p.category_id
  )

Upvotes: 0

Related Questions