Solved

ms sql 2008 recursion?

Posted on 2012-03-29
5
311 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
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
(Classic bill-of-materials essentially)
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
List the required result.
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now