Dear All.

I'm very new to ASA. I would like to develop an application for my company, it's production company so it requires to have BOM. Let's say I have a table which recursive relationship as follow.


  • item_id
  • qty
  • parent_item_id

Just like usual table structure found on the internet, for the parent item, it's parent_item_id contains null. My question is that how to query one single parent item with all it's child (no matter how many level will be) at once? Do I need a stored procedure or this can be done using plain SQL statement? Please give me some samples or hits.

Thanks Kongthap

asked 06 Apr '12, 06:01

Kongthap's gravatar image

accept rate: 0%

edited 15 Mar '13, 21:14

Mark%20Culp's gravatar image

Mark Culp

Have a look at Parts explosion problems.

permanent link

answered 06 Apr '12, 08:50

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

Wow. Save that one for your next book. :)

(07 Apr '12, 12:19) dejstone
Replies hidden

Hah, hah... there is an example in my last, um, only book. This question is making me think I'll self-plagiarize republish it in my blog :)

(07 Apr '12, 17:51) Breck Carter

FWIW: The cited solution requires recursive common table expressions (CTEs) and as such, requires v9 and above - so for older versions, I guess a stored procedure will be necessary.

(08 Apr '12, 05:57) Volker Barth

Breck has republished the according part of his book in his blog (and I feel free to publish the link:))


(11 Apr '12, 07:37) Volker Barth

Following the question: How fast is "RECURSIVE UNION"? Each level is permanent 'select' and in case of distributed databases with multiple tables ? Any Evaluation Tests...?!

permanent link

answered 27 Mar '13, 05:20

Irakli's gravatar image

accept rate: 0%

edited 27 Mar '13, 05:21

As this certainly is a differen question, I'd recommend to ask it as a separate question - and you can of course link to this current FAQ there...

(27 Mar '13, 05:25) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 06 Apr '12, 06:01

question was seen: 4,506 times

last updated: 27 Mar '13, 05:25