Hi there,
I would like to know if anyone knows a way to solve the problem below with a SQL querie, maybe using some hierarchical queries or window functions (or anything else in SQL for that matter).
My environment is:
Oracle Database 11g Release 11.2.0.2.0 - 64bit
The problem is this:
I have a list of items that are grouped together in two different grouping ways (two columns).
This gives the ability for items to be linked to other items in two ways:
1. Directly if both have same value on GROUP1 and/or GROUP2;
2. indirectly if they have an item in common with at least one match on either GROUP1 or GROUP2.
The idea is to start from this dataset:
WITH T AS
(
SELECT 1 AS ITEM_ID, 'A' AS GROUP1, 100 AS GROUP2 FROM DUAL UNION
SELECT 2 AS ITEM_ID, 'A' AS GROUP1, 100 AS GROUP2 FROM DUAL UNION
SELECT 3 AS ITEM_ID, 'A' AS GROUP1, 101 AS GROUP2 FROM DUAL UNION
SELECT 4 AS ITEM_ID, 'B' AS GROUP1, 100 AS GROUP2 FROM DUAL UNION
SELECT 5 AS ITEM_ID, 'B' AS GROUP1, 102 AS GROUP2 FROM DUAL UNION
SELECT 6 AS ITEM_ID, 'C' AS GROUP1, 103 AS GROUP2 FROM DUAL UNION
SELECT 7 AS ITEM_ID, 'D' AS GROUP1, 101 AS GROUP2 FROM DUAL
)
SELECT * FROM T;
And end up with this dataset with a one single joint grouping:
WITH T AS
(
SELECT 1000 AS JOINT_GROUP_ID, 1 AS ITEM_ID FROM DUAL UNION
SELECT 1000 AS JOINT_GROUP_ID, 2 AS ITEM_ID FROM DUAL UNION
SELECT 1000 AS JOINT_GROUP_ID, 3 AS ITEM_ID FROM DUAL UNION
SELECT 1000 AS JOINT_GROUP_ID, 4 AS ITEM_ID FROM DUAL UNION
SELECT 1000 AS JOINT_GROUP_ID, 5 AS ITEM_ID FROM DUAL UNION
SELECT 1000 AS JOINT_GROUP_ID, 7 AS ITEM_ID FROM DUAL UNION
SELECT 2000 AS JOINT_GROUP_ID, 6 AS ITEM_ID FROM DUAL
)
SELECT * FROM T;
The relationships are:
Item 1 is linked to Item 2 by GROUP1 and GROUP2;
Item 1 is linked to Item 3 by GROUP1 only;
Item 1 is linked to Item 4 by GROUP2 only;
Item 1 is linked to Item 5 through Item 4 by GROUP1;
Item 1 is linked to Item 7 through Item 3 by GROUP2;
Item 6 is not linked to any other item since it does not match on GROUP1 nor GROUP2 with any other item.
NOTEs:
- JOINT_GROUP_ID values could be any sequential value. I used 1000 and 2000 just to avoid confusion with the other IDs and group values used to picture the problem.
- The level of relationship is not restricted to 2 like the example above. There could be deeper relationships.
This seems to me like something that could be solved with a hierarchical query, but I could not get my head around it to solve the problem.
Hope one of you guys can help me on this.
Chears.