Solved

ms sql 2008 recursion?

Posted on 2012-03-29
5
314 Views
Last Modified: 2012-03-30
I have a list of items,  but some "items" are kits of other items. I am trying to get a list of all  physical.final items.

Example Items a,b,c,d (kit with a,b), e, f (kit c,d),
There is another table that lists if item is a "final item"  - the opposite being a kit.

Selecting all items, I would like to get the list:
a
b
c
a (for kit d)
b (for kit d)
e
c (for kit f)
a (for kit f,d)
b (for kit f,d)
0
Comment
Question by:freshgrill
5 Comments
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 37784067
Hi,
Kindly provide the structure of your two tables. That will be easy to provide the solution.
Because when we say the data is like
a,b,c,d (kit with a,b),
Then what the column values are, is it like:
Key1 Key2
A       A
B       B
C       C
D       A
D       A
or whatever....
0
 

Author Comment

by:freshgrill
ID: 37784113
tableA

item, final
a  yes
b yes
c yes
d no
e yes
f no

pkg_list
pkg_num item_num
a       a
b       b
c      a
c      a
d      d
e      a
f      f
g    c
g    e

Basically, 1 table that list a pkg/item number and the pkg contents (i.e. items in the pkg). and a second table that list all items and if they are a "final" item or a kit.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37784203
(Classic bill-of-materials essentially)
0
 
LVL 30

Expert Comment

by:hnasr
ID: 37784443
List the required result.
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
ID: 37785087
Hi,
Considering the structure of your tables, try the below code:

with childList (item, child, remarks) as (
select a.item_num, a.pkg_num, convert(varchar(100),a.pkg_num) as remarks  
from pkg_list a, tableA b where a.pkg_num = b.item and b.final = 'no' 
union all 
select a.item_num, b.item, convert(varchar(100),b.remarks + '->' + b.item) as remarks 
from childList b, pkg_list a, tableA c  where b.item = a.pkg_num and b.item = c.item and c.final = 'no'
) 
select a.pkg_num, '' for_kit 
from pkg_list a, tableA b where a.pkg_num = b.item and b.final = 'yes' 
union all 
select a.item, a.remarks 
from childList a, tableA b where a.item = b.item and b.final = 'yes' 
order by 2

Open in new window

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question