0

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;
sam
  • 2,493
  • 6
  • 38
  • 73
  • First the query duplicate the records because you don't add a where condition to relation the B table with A, then for each record on B table duplicate the result on A table. – hackvan Mar 17 '17 at 20:31

1 Answers1

0
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
  )
B Samedi
  • 380
  • 3
  • 11