CODEWITHSUNDEEP
sqlsql-servercommon-table-expressionrecursive-query
Leo
Leo

Reputation: 109

SQL Server Recursive Query to get Top parent

I need your help on making on recursive query using a CTE in SQL Server.

I have order no as input parameter.. I need to display its top parent despatch details. even if I search for its children.. ie if I search for order no 3 I should get its top parent order no. that is 20.

Here is my 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  INTO #TblSerialNo   VALUES  ( 'TS1', 'DD123CV1' )
INSERT  INTO #TblSerialNo   VALUES  ( 'TS2', 'DD123CV2' )
INSERT  INTO #TblSerialNo  VALUES   ( 'TS3', 'DD123CV3' )

INSERT  INTO #TblSerialNo VALUES  ( 'BS1', 'DD12sfs3CV1' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS2', 'DD1et23CV2' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS1', 'DD12e3CV1' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS2', 'DD12fe3CV2' )

INSERT  INTO #TblSerialNo VALUES  ( 'BS1aa', 'DD12d3CV1' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS1ab', 'DDd123CV2' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS1ac', 'DD1r23CV3' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS2aa', 'DDs123CV4' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS2ab', 'DD12d3CV1' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS2ac', 'DD1s23CV2' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS1aa', 'DD1s23CV3' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS1ab', 'DD12s3CV4' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS1ac', 'DD123dCV1' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS2aa', 'DDa123CV2' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS2ab', 'DDa123CV3' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS2ac', 'DDa123CV4' )

--================  Relation table   ==============

INSERT  INTO #TblSerialReleation  VALUES  ( 'TS1', 'BS1' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'TS1', 'BS2' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'TS2', 'CS1' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'TS2', 'CS2' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS1', 'BS1aa' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS1', 'BS1ab' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS1', 'BS1ac' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS2', 'BS2aa' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS2', 'BS2ab' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS2', 'BS2ac' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS1', 'CS1aa' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS1', 'CS1ab' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS1', 'CS1ac' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS2', 'CS2aa' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS2', 'CS2ab' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS2', 'CS2ac' )
--===========  Despatch 
INSERT  INTO #TblDespatch  VALUES  ( 'CS2ac', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS2ab', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS2ac', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS1aa', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS1ac', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS2ac', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS2ac', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'TS1', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'TS3', 2 )
INSERT  INTO #TblDespatch  VALUES  ( 'TS2', 3 )
INSERT  INTO #TblDespatch  VALUES  ( 'BS2ab', 20 )

DROP TABLE #TblDespatch
DROP TABLE #TblSerialNo
DROP TABLE #TblSerialReleation

Thanks in advance.

Upvotes: 1

Views: 2390

Answers (1)

Cristian Lupascu
Cristian Lupascu

Reputation: 40526

Looking at your data, the relation seems to go the other way around (BS2ab (Order 20) is the child of TS1(Order 3) through BS2.

If this is the case, starting from the child (BS2ab) you can find its top parent with the following statement:

;with ParentOrders as(
  select 
    convert(char(20), 'BS2ab')as SRno,
    0 as Level 

  union  all

  select r.SRno, o.Level + 1
  from ParentOrders o
  join TblSerialReleation r
    on o.SRNo = r.ChildSRno 
)
select top 1 SRNO 
  from ParentOrders
  order by Level desc

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

Upvotes: 2

Related Questions