query for relation between 2 fields

[LudoS]

Hi All, I hope someone is able to help me with this.

I am having a table with partnumber(s) like shown here (actually a access2003 database)

ID (autonumber, primary key)
Partnumber (text)
Replacment (text)
Description (text)
Comment (text)
..... <Some other fields>.

Data look like this:
ID Partnumber Replacement Description Comment
x 5030331-001 Partx
x 5030332-001 5030332-002 Part1 from vendor 1 Obsolete
x 5030332-002 8063737-001 Part1 from vendor 1 Obsolete
x 5030333-001 Partx
x .....
x 8063737-001 Part1 from vendor2

There are a lot of Partnumbers which are obsolete (this partnumber has a replacement part). I am searching for a query where (with one parameter, = partnumber to look for), where i can see complete relation. So if I am searching for partnumber 8063737-001 I get same result as when I am searching for 5030332-001 or 5030332-002.

Result should be like this:
ID Partnumber Replacment Description Comment ...........
x 5030332-001 5030332-002 Part1 from vendor 1 Obsolete
x 5030332-002 8063737-001 Part1 from vendor 1 Obsolete
x 8063737-001 Part1 from vendor2


has anyone any idea how to create this kind of query?

Ludo

[nbiswas]

Hi,
I have made a table populated with some data based on what I have understood.
Then I am using a recursive CTE for fetching the result.
I am using SQL SERVER 2005+

Test the test table first and include the appropriate data over there and then run the query.

--Table creation and sample data population

declare @t table(
    ID int identity
    ,Partnumber  varchar(100)
    ,Replacment varchar(100)
    ,Description varchar(100)
    ,Comment varchar(100))
insert into @t
    select '5030331-001','Part 1',null,null union all
    select '5030332-001','5030332-002','Part1 from vendor 1','Obsolete' union all
    select '5030332-002','8063737-001','Part1 from vendor 1','Obsolete' union all
    select '5030333-001','Part 4',null,null union all
    select '8063737-001',null,'Part1 from vendor 2',null

The output of the sample table is

ID    Partnumber    Replacment    Description    Comment
1    5030331-001    Part 1    NULL    NULL
2    5030332-001    5030332-002    Part1 from vendor 1    Obsolete
3    5030332-002    8063737-001    Part1 from vendor 1    Obsolete
4    5030333-001    Part 4    NULL    NULL
5    8063737-001    NULL    Part1 from vendor 2    NULL

Based on the above table structure, here is the program

;with cte as
(
    select id,Partnumber,Replacment,Description,Comment
    from @t
    where Partnumber = '8063737-001' -- Enter the partnumber here
    union all
    select t.id,t.Partnumber,t.Replacment,t.Description,t.Comment
    from @t t
    inner join 
    cte c
    on c.Partnumber = t.Replacment
)
 
select * from cte
order by id
option (maxrecursion 0)

And the output is:

id    Partnumber    Replacment    Description    Comment
2    5030332-001    5030332-002    Part1 from vendor 1    Obsolete
3    5030332-002    8063737-001    Part1 from vendor 1    Obsolete
5    8063737-001    NULL    Part1 from vendor 2    NULL

Hope this helps.

Let me know in case of any concern