October 11, 2012 at 11:38 am
This is a SQL Question, hopefully not difficult.
The example is simplified but should work. Have a table with basically two columns
Part
ExtPart
Below are some sample rows from this sample table
Part ExtPart
ABC 123
DEF 001
XYZ 456
123 DEF
PDQ 789
001 005
001 XYZ
What I need is someting similar to a recursive function/method. I want to search for all ExtPart's that have a Part of 'ABC'. Simple enough that would get me a ExtPart of 123. However since 123 is also has an entry in the table in Part column I need the query to return that. So it would be Give me any ExtParts that have a Part of 'ABC' and then any Part's that use the ExtPart we just returned. (Hope I didn't make it more confusing). The result set below would be what I want when I search for Part 'ABC'.
Part Ext Part
ABC 123
123 DEF
DEF 001
001 005
001 XYZ
Any and all suggestions are appreciated. I have some limited ideas on this and know it should be much more straight forward.
Thanks
k
October 11, 2012 at 12:26 pm
A recursive cte can do what you want here. Something like this.
create table #Parts
(
Part varchar(10),
ExtPart varchar(10)
)
insert #Parts
select 'ABC', '123' union all
select 'DEF', '001' union all
select 'XYZ', '456' union all
select '123', 'DEF' union all
select 'PDQ', '789' union all
select '001', '005' union all
select '001', 'XYZ'
declare @Part varchar(10) = 'abc'
;with cte as
(
select Part, ExtPart from #Parts where Part = @Part
union all
select p.Part, p.ExtPart
from #Parts p
join cte on cte.ExtPart = p.Part
)
select * from cte
select * from #Parts
drop table #Parts
You did not include the row 'XYZ', '456' but it should be in your result because there are two part #s with 001 and 001 belongs as a child to ABC about 3 levels deep.
Notice how I posted a table and sample data so we can all just execute and test code. That will go a long way on future posts. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply