I have a table with categories and another table with a discount for categories per user.
create table category
(
id NUMBER NOT NULL,
parent_id NUMBER,
name nvarchar2(255),
PRIMARY KEY( id)
);
create table category_discount
(
user_id NUMBER NOT NULL,
category_id NUMBER NOT NULL,
discount NUMBER NOT NULL,
PRIMARY KEY( user_id, category_id)
);
insert into category( id, parent_id, name ) VALUES( 1, null, 'root');
insert into category( id, parent_id, name ) VALUES( 2, 1, 'C1');
insert into category( id, parent_id, name ) VALUES( 11, 7, 'C11');
insert into category( id, parent_id, name ) VALUES( 12, 7, 'C12');
insert into category( id, parent_id, name ) VALUES( 3, 1, 'C3');
insert into category( id, parent_id, name ) VALUES( 4, 1, 'C4');
insert into category( id, parent_id, name ) VALUES( 5, 4, 'C5');
insert into category( id, parent_id, name ) VALUES( 6, 4, 'C6');
insert into category( id, parent_id, name ) VALUES( 9, 6, 'C9');
insert into category( id, parent_id, name ) VALUES( 10, 6, 'C10');
insert into category( id, parent_id, name ) VALUES( 7, 1, 'C7');
insert into category_discount( user_id, category_id, discount ) VALUES( 1, 1, 30);
insert into category_discount( user_id, category_id, discount ) VALUES( 1, 4, 20);
insert into category_discount( user_id, category_id, discount ) VALUES( 1, 7, 25);
Now when i do this query
SELECT category.*, category_discount.user_id, category_discount.discount, LEVEL FROM category
LEFT JOIN category_discount ON category.id = category_discount.category_id AND category_discount.user_id = 1
START WITH parent_id is null
CONNECT BY PRIOR id = parent_id;
i get this output which is fine
| ID | PARENT_ID | NAME | USER_ID | DISCOUNT | LEVEL |
|----|-----------|------|---------|----------|-------|
| 1 | (null) | root | 1 | 30 | 1 |
| 2 | 1 | C1 | (null) | (null) | 2 |
| 3 | 1 | C3 | (null) | (null) | 2 |
| 4 | 1 | C4 | 1 | 20 | 2 |
| 5 | 4 | C5 | (null) | (null) | 3 |
| 6 | 4 | C6 | (null) | (null) | 3 |
| 9 | 6 | C9 | (null) | (null) | 4 |
| 10 | 6 | C10 | (null) | (null) | 4 |
| 7 | 1 | C7 | (null) | 25 | 2 |
| 11 | 7 | C11 | (null) | (null) | 3 |
| 12 | 7 | C12 | (null) | (null) | 3 |
My requirement is whenever there is a discount on the parent it should be inherited down the hierarchy as long as the child doesn't haven an explicit discount set. So my final output should look like this
| ID | PARENT_ID | NAME | USER_ID | DISCOUNT | LEVEL |
|----|-----------|------|---------|----------|-------|
| 1 | (null) | root | 1 | 30 | 1 |
| 2 | 1 | C1 | (null) | 30 | 2 |
| 3 | 1 | C3 | (null) | 30 | 2 |
| 4 | 1 | C4 | 1 | 20 | 2 |
| 5 | 4 | C5 | (null) | 20 | 3 |
| 6 | 4 | C6 | (null) | 20 | 3 |
| 9 | 6 | C9 | (null) | 30 | 4 |
| 10 | 6 | C10 | (null) | 30 | 4 |
| 7 | 1 | C7 | (null) | 25 | 2 |
| 11 | 7 | C11 | (null) | 25 | 3 |
| 12 | 7 | C12 | (null) | 25 | 3 |
How can i do this ? Btw i am using ORACLE 11g and ORACLE 12c
