How to get parent's child and grandchild records by providing parent name only

Question

I have to get the child records from Parent and child table. If someone is familiar with the BOM concept Parent Item from ia table can have many child records and one of it child can also have many children and all the information is saved in BOM table. The query and the result is something like this

CREATE TABLE ITEM(ID, ITEM_NUMBER)'

INSERT INTO ITEM VALUES (1, 'ITEM-A');
INSERT INTO ITEM VALUES (2, 'ITEM-A1');
INSERT INTO ITEM VALUES (3, 'ITEM-A2');
INSERT INTO ITEM VALUES (4, 'ITEM-A2');
INSERT INTO ITEM VALUES (5, 'ITEM-A2-1');
INSERT INTO ITEM VALUES (6, 'ITEM-A2-2');


CREATE TABLE BOM(ID, PARENT_ITEM, COMPONENT);

INSERT INTO BOM VALUES (1, 1, 2);
INSERT INTO BOM VALUES (2, 1, 3);
INSERT INTO BOM VALUES (3, 1, 4);
INSERT INTO BOM VALUES (4, 2, 5);
INSERT INTO BOM VALUES (5, 2, 6);


SELECT ia.ITEM_NUMBER PARENT_ITEM, ic.ITEM_NUMBER COMPONENT
FROM BOM
INNER JOIN AGILE.ITEM ia ON BOM.ITEM = ia.id
INNER JOIN AGILE.ITEM ic ON ic.id = BOM.COMPONENT
WHERE ia.ITEM_NUMBER = 'ITEM-A'

This query will return result like this.

PARENT_ITEM      COMPONENT
ITEM-A           ITEM-A1
ITEM-A           ITEM-A2
ITEM-A           ITEM-A3

What I want is if lets say ITEM-A2 have 2 child records it self the query which I am trying to build should return records something like this

PARENT_ITEM      COMPONENT
ITEM-A           ITEM-A1
ITEM-A           ITEM-A2
ITEM-A           ITEM-A3
ITEM-A2          ITEM-A2-1
ITEM-A2          ITEM-A2-2

It's not very clear what you want. Please provide the input data which coresponds to the expected data. — Thomas Strub, Mar 05 '20 at 15:19
Does this answer your question? [Recursion In Oracle](https://stackoverflow.com/questions/4659803/recursion-in-oracle) — Aaron Dietz, Mar 05 '20 at 15:22
if this is only for direct children you can. 1. Create subquery where you do a self join and get the children 2. union with the query you provided — lukaszberwid, Mar 05 '20 at 15:24
@AaronDietz I've been to that post already but that didn't end quite well in my case — Muhammad Asim, Mar 05 '20 at 15:24
@lukaszberwid I want to provide the name of 'ITEM-A' in where clause and it should get all the family records under this to nth level. In this case all 5 rows — Muhammad Asim, Mar 05 '20 at 15:26

score 1 · Accepted Answer · answered Mar 05 '20 at 15:54

1

Looks like you need this hierarchical query:

select ii.item_number, ic.item_number 
  from bom 
  join item ii on ii.id = bom.parent_item
  join item ic on ic.id = bom.component
  connect by prior component = parent_item
  start with parent_item = (select id from item where item_number = 'ITEM-A')

^dbfiddle

answered Mar 05 '20 at 15:54

Ponder Stibbons

14,723
2
21
24

This is exactly what I needed but there is a glitch which you might be able to resolve too. If I put 2 item numbers in the query like this `parent_item = (select id from item where item_number IN ('ITEM-A', 'ITEM-A2'))` it gives me an error. `ORA-01427: single-row subquery returns more than one row` – Muhammad Asim Mar 05 '20 at 17:16
The thing is I get the item numbers from another team and we don't know which parent item is also being used as child item in another rows so in that case I might get 2 item numbers 'ITEM-A' and 'ITEM-A2' so query should just give me 5 rows in that case too. In short it should ignore such values. – Muhammad Asim Mar 05 '20 at 17:18
I'm sure this can be achieved with distinct but first this error must go away. – Muhammad Asim Mar 05 '20 at 17:19
1

You can't use `=` for query which return two rows. Use `in` twice: `parent_item in (select id from item where item_number IN ('ITEM-A', 'ITEM-A2'))` – Ponder Stibbons Mar 06 '20 at 10:15

How to get parent's child and grandchild records by providing parent name only

1 Answers1