May 7, 2007 at 9:39 am
Folks,
I have this issue on SQL Server 2000:
Table: Part (pno int, pname varchar(25), old_pno int)
A part can be renamed to another part and the new part will be created as a new record. The old part number will be populated into old_pno of the new part record. The data in this table will be like:
pno pname old_pno
----------- ---------------------- -----------
1 Andhra01 NULL
2 Andhra02 1
3 Andhra02 2
4 NULL NULL
Now, I want to find out the chain of parts for part number 3, (ie) I need to get parts with pno: 1, 2 and 3. Please suggest how to write the query.
thanks in advance.
M.
May 8, 2007 at 6:57 am
Take a look at today's article on CTE's. It shows several methods that might help with this question:
http://www.sqlservercentral.com/columnists/phe/2926.asp
Depending on how complicated you want to get with the query, you could simply join the table to itself.
SELECT t1.pname
FROM table t1
JOIN table t2
ON t1.pno = t2.old_pno
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply