Oracle 12c Recursive query for Product , Category relationship

Question

I have data like below. If i search Product "P1" then

1. I need all the category which has the product "P1"

2. I need all the products that are related to the category from step 1

Explanation: Product P1 avail in Categories in C1,C2,C4. So, I'll consider all the products from these categories and category C2 contains two products which are P1,P4.

So, I have to consider category C3 as well because product P4 avail in C3. Similar way to check for all the records and get data.

I can achieve this using Cursor, for loops in stored procedure and temp tables. Is there any way to get data by using query?

Answer 1

A Simple SQL query like below should do the job,

select * from products 
where category in 
(select category 
 from products 
 where product in 
(select product 
from products 
where category in (
(select category 
from products 
where product ='P1'))))

Output :

Answer 2

You can use EXISTS as follows:

SQL> -- SAMPLE DATA
SQL> WITH YOUR_TABLE (CATEGORY, PRODUCT,AMOUNT) AS
  2  (SELECT 'C1','P1',5 FROM DUAL UNION ALL
  3  SELECT 'C1','P2',6 FROM DUAL UNION ALL
  4  SELECT 'C2','P4',5 FROM DUAL UNION ALL
  5  SELECT 'C2','P1',10 FROM DUAL UNION ALL
  6  SELECT 'C3','P4',20 FROM DUAL UNION ALL
  7  SELECT 'C3','P5',5 FROM DUAL UNION ALL
  8  SELECT 'C3','P3',10 FROM DUAL UNION ALL
  9  SELECT 'C4','P1',5 FROM DUAL UNION ALL
 10  SELECT 'C5','P7',5 FROM DUAL UNION ALL
 11  SELECT 'C5','P8',5 FROM DUAL UNION ALL
 12  SELECT 'C5','P9',5 FROM DUAL)
 13  -- YOUR QUERY STARTS FROM HERE
 14  SELECT *
 15    FROM YOUR_TABLE T
 16   WHERE T.PRODUCT = 'P1'
 17      OR EXISTS (
 18  SELECT 1
 19    FROM YOUR_TABLE   P1LVL1
 20    JOIN YOUR_TABLE   P1LVL2
 21  ON P1LVL2.PRODUCT = P1LVL1.PRODUCT
 22    JOIN YOUR_TABLE   P1LVL3
 23  ON P1LVL3.CATEGORY = P1LVL2.CATEGORY
 24     AND T.CATEGORY = P1LVL1.CATEGORY
 25     AND P1LVL3.PRODUCT = 'P1'
 26  )
 27   ORDER BY CATEGORY,
 28            PRODUCT;

CA PR     AMOUNT
-- -- ----------
C1 P1          5
C1 P2          6
C2 P1         10
C2 P4          5
C3 P3         10
C3 P4         20
C3 P5          5
C4 P1          5

8 rows selected.

SQL>