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]

Solution to query for relation between 2 fields

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

[B]ID	Partnumber	Replacment	Description	Comment[/B]
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:

[B]id	Partnumber	Replacment	Description	Comment[/B]
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