SQL Server Recursive Query to get Top parent -

SQL Server Recursive Query to get Top parent -

i need help on making on recursive query using cte in sql server.

i have order no input parameter.. need display top parent despatch details. if search children.. ie if search order no 3 should top parent order no. 20.

here table structure..

create table #tblserialno ( [srno] [char](20) not null , [custsrno] [varchar](75) null ) create table #tblserialreleation ( [srno] [char](20) not null , [childsrno] [char](20) not null ) create table #tbldespatch ( [srno] [char](20) not null , orderno int not null ) insert #tblserialno values ( 'ts1', 'dd123cv1' ) insert #tblserialno values ( 'ts2', 'dd123cv2' ) insert #tblserialno values ( 'ts3', 'dd123cv3' ) insert #tblserialno values ( 'bs1', 'dd12sfs3cv1' ) insert #tblserialno values ( 'bs2', 'dd1et23cv2' ) insert #tblserialno values ( 'cs1', 'dd12e3cv1' ) insert #tblserialno values ( 'cs2', 'dd12fe3cv2' ) insert #tblserialno values ( 'bs1aa', 'dd12d3cv1' ) insert #tblserialno values ( 'bs1ab', 'ddd123cv2' ) insert #tblserialno values ( 'bs1ac', 'dd1r23cv3' ) insert #tblserialno values ( 'bs2aa', 'dds123cv4' ) insert #tblserialno values ( 'bs2ab', 'dd12d3cv1' ) insert #tblserialno values ( 'bs2ac', 'dd1s23cv2' ) insert #tblserialno values ( 'cs1aa', 'dd1s23cv3' ) insert #tblserialno values ( 'cs1ab', 'dd12s3cv4' ) insert #tblserialno values ( 'cs1ac', 'dd123dcv1' ) insert #tblserialno values ( 'cs2aa', 'dda123cv2' ) insert #tblserialno values ( 'cs2ab', 'dda123cv3' ) insert #tblserialno values ( 'cs2ac', 'dda123cv4' ) --================ relation table ============== insert #tblserialreleation values ( 'ts1', 'bs1' ) insert #tblserialreleation values ( 'ts1', 'bs2' ) insert #tblserialreleation values ( 'ts2', 'cs1' ) insert #tblserialreleation values ( 'ts2', 'cs2' ) insert #tblserialreleation values ( 'bs1', 'bs1aa' ) insert #tblserialreleation values ( 'bs1', 'bs1ab' ) insert #tblserialreleation values ( 'bs1', 'bs1ac' ) insert #tblserialreleation values ( 'bs2', 'bs2aa' ) insert #tblserialreleation values ( 'bs2', 'bs2ab' ) insert #tblserialreleation values ( 'bs2', 'bs2ac' ) insert #tblserialreleation values ( 'cs1', 'cs1aa' ) insert #tblserialreleation values ( 'cs1', 'cs1ab' ) insert #tblserialreleation values ( 'cs1', 'cs1ac' ) insert #tblserialreleation values ( 'cs2', 'cs2aa' ) insert #tblserialreleation values ( 'cs2', 'cs2ab' ) insert #tblserialreleation values ( 'cs2', 'cs2ac' ) --=========== despatch insert #tbldespatch values ( 'cs2ac', 1 ) insert #tbldespatch values ( 'cs2ab', 1 ) insert #tbldespatch values ( 'cs2ac', 1 ) insert #tbldespatch values ( 'cs1aa', 1 ) insert #tbldespatch values ( 'cs1ac', 1 ) insert #tbldespatch values ( 'cs2ac', 1 ) insert #tbldespatch values ( 'cs2ac', 1 ) insert #tbldespatch values ( 'ts1', 1 ) insert #tbldespatch values ( 'ts3', 2 ) insert #tbldespatch values ( 'ts2', 3 ) insert #tbldespatch values ( 'bs2ab', 20 ) drop table #tbldespatch drop table #tblserialno drop table #tblserialreleation

thanks in advance.

looking @ data, relation seems go other way around (bs2ab (order 20) kid of ts1(order 3) through bs2.

if case, starting kid (bs2ab) can find top parent next statement:

;with parentorders as( select convert(char(20), 'bs2ab')as srno, 0 level union select r.srno, o.level + 1 parentorders o bring together tblserialreleation r on o.srno = r.childsrno ) select top 1 srno parentorders order level desc

here's working sample of query: http://www.sqlfiddle.com/#!3/e253e/6

sql sql-server common-table-expression recursive-query