sql server 2008 - SQL loop through a table and find the next part record -

sql server 2008 - SQL loop through a table and find the next part record -

not sure start on one. inheriated table has list of part numbers are active , inactive. if part number inactive, come in next valid part number. if part number active there no next partnumber. want search on part number , find of next part numbers match.

basically table looks this. partnumber varchar(20), active varchar(3), nextpartnumber varchar(20).

problem not know how many part numbers in chain. here sample of data:

100x no xyz xyz no 45a6 45a6 yes qwer no rt98 rt98 no poul1 poul1 no n9hgt n9hgt no fgh12 fgh12 yes

i can write query this, since don't know how many part numbers there are, won't work.

select a.partnumber, a.nextpartnumber, b.partnumber, b.nextpartnumber, c.partnumber, c.nextpartnumber tblparttable inner bring together tblparttable b on a.partnumber = b.nextpartnumber inner bring together tblparttable c on b.partnumber = c.nextpartnumber a.partnumber = '100x'

with sql server (which i'm assuming you're talking since before questions have been it), can utilize recursive mutual table look searched part , successors, there no need loop manually;

with cte ( -- base of operations condition, start search? select t.* tblparttable t t.partnumber = '100x' union -- go on condition, how find next part current one? select t.* tblparttable t bring together cte on t.partnumber = cte.nextpartnumber ) select partnumber, active cte;

an sqlfiddle test with.

the same query works on rdbms's except mysql.

sql sql-server-2008