Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Aug 2007
    Posts
    11

    Question Unanswered: Recursive queries

    Dear

    I have 2 tables
    Tab1 Tab2
    CID Cname TabID CID BasedOn
    1 A 1 1 3
    2 B 2 1 4
    3 C 3 3 5
    4 D 4 4 8
    5 E 5
    6 F 6
    7 G 7
    8 H

    The above Tab1 is parent table. and Tab2 is child.
    I want output such that, if Tab1.CID = tab2.CID and Tab2.BasedOn = tab1.CID and if tab2.BasedOn is not linked to any it should return null.

    The expected output should be like

    CID TabID BasedOn Group
    1 1 3 1
    3 3 5 1
    5 NULL NULL 1
    4 4 8 2
    8 NULL NULL 2

    From above output, CID 1 belongs to Tab2 with TabID=1 which is based on 3 and Tab1.CID=3 is based on 5 and tab2.BasedOn=5 is based on none so null in next row.
    I want to get the chain of basedon.

    how can i structure this.
    Need help of experts.

    Million thanks in advance.

    Regards
    Attached Thumbnails Attached Thumbnails recu.jpg  

    --
    Shailesh Patil

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Write sql statements to produce the results.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Can't figure out your data. What belongs to Tab1 and what belongs to Tab2?
    Post DDL and sample data please, along with any SQL you've already tried.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Blindman, this may help you. I started with something, but I can't get it to work.
    Code:
    CREATE TABLE #TAB1(
    	CID	INT,
    	Cname	CHAR(1)
    )
    INSERT INTO #TAB1(CID, Cname) VALUES
    (1, 'A'), 
    (2, 'B'), 
    (3, 'C'), 
    (4, 'D'), 
    (5, 'E'), 
    (6, 'F'), 
    (7, 'G'), 
    (8, 'H') 
    
    CREATE TABLE #TAB2(
    	TabID	INT,
    	CID		INT,
    	BasedOn	INT
    )
    INSERT INTO #TAB2(TabID, CID, BasedOn) VALUES
    (1, 1, 3),
    (2, 1, 4),
    (3, 3, 5),
    (4, 4, 8),
    (5, NULL, NULL),
    (6, NULL, NULL),
    (7, NULL, NULL)
    
    --if Tab1.CID = tab2.CID and Tab2.BasedOn = tab1.CID and 
    -- if tab2.BasedOn is not linked to any it should return null.
    
    --CID	TabID	BasedOn	Group
    --1		1		3		1
    --3		3		5		1
    --5		NULL	NULL	1
    --4		4		8		2
    --8		NULL	NULL	2
    ;WITH CTE AS
    (SELECT 
    	CID, 
    	TabID, 
    	BasedOn, 
    	ROW_NUMBER() OVER (PARTITION BY CID) AS RowNum
    FROM #TAB2
    WHERE #TAB2.BasedOn IS NOT NULL AND
    	EXISTS (SELECT 1 FROM #TAB2 as T2 WHERE #TAB2.BasedOn = T2.CID)
    ORDER BY CID
    
    UNION ALL
    
    SELECT 
    	CTE.BasedOn, 
    	#TAB2.TabID, 
    	#TAB2.BasedOn, 
    	CTE.RowNum
    FROM CTE
    	INNER JOIN #TAB2 ON
    		CTE.BasedOn = #TAB2.CID
    )
    SELECT *
    FROM CTE
    ORDER BY RowNum, CID
    Last edited by Wim; 04-18-12 at 14:34.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?


    Note: I changed "RowNum" to "Group" to meet your original "The expected output".
    (Not tested)
    Code:
    ...
    UNION ALL
    SELECT 
    	CTE.BasedOn, 
    	#TAB2.TabID, 
    	NULLIF(#TAB2.BasedOn , CTE.BasedOn) AS BasedOn, 
    	CTE.Group
    FROM CTE
       , #TAB2
     WHERE
      (    CTE.BasedOn = #TAB2.CID
       OR  NOT EXISTS
          (SELECT 0
            FROM  #TAB2 as t2e
            WHERE CTE.BasedOn = t2e.CID
          )
       AND CTE.BasedOn = #TAB2.BasedOn
      )
    )
    ...
    By the way,
    I thought Tab1 is not neccesary to get the expected output.
    And, you didn't use Tab1 in your query.
    Last edited by tonkuma; 04-18-12 at 14:51. Reason: Remove "AND CTE.BasedOn IS NOT NULL". Replace "AND TAB2.CID = CTE.CID" with "AND CTE.BasedOn = #TAB2.BasedOn".

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The expected output should be like

    CID TabID BasedOn Group
    1 1 3 1
    3 3 5 1
    5 NULL NULL 1
    4 4 8 2
    8 NULL NULL 2
    Why "1 2 4 2" was not in the output?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought
    Code:
    WHERE #TAB2.BasedOn IS NOT NULL AND
    	EXISTS (SELECT 1 FROM #TAB2 as T2 WHERE #TAB2.BasedOn = T2.CID)
    should be
    Code:
    WHERE #TAB2.BasedOn IS NOT NULL AND
    	NOT EXISTS (SELECT 1 FROM #TAB2 as T2 WHERE #TAB2.CID = T2.BasedOn)
    if relationships of "BasedOn ---> CID" were repeated more than two times.

  8. #8
    Join Date
    Aug 2007
    Posts
    11
    Dear All

    Thank you very much for your quick and immediate support.
    With the example you provided, i happened to modify the query with my original table and it gave me error. please find script below

    Code:
    Declare @TransactionCertificate_ID INT
    
    SET @TransactionCertificate_ID = 115572
    
    drop table #TransactionCertificate_T
    CREATE TABLE #TransactionCertificate_T(
    	TransactionCertificate_ID INT,
    	Cname	CHAR(1)
    )
    INSERT INTO #TransactionCertificate_T(TransactionCertificate_ID, Cname) VALUES (1, 'A')
    INSERT INTO #TransactionCertificate_T(TransactionCertificate_ID, Cname) VALUES (2, 'B')
    INSERT INTO #TransactionCertificate_T(TransactionCertificate_ID, Cname) VALUES (3, 'C')
    INSERT INTO #TransactionCertificate_T(TransactionCertificate_ID, Cname) VALUES (4, 'D')
    INSERT INTO #TransactionCertificate_T(TransactionCertificate_ID, Cname) VALUES (5, 'E')
    INSERT INTO #TransactionCertificate_T(TransactionCertificate_ID, Cname) VALUES (6, 'F')
    INSERT INTO #TransactionCertificate_T(TransactionCertificate_ID, Cname) VALUES (7, 'G')
    INSERT INTO #TransactionCertificate_T(TransactionCertificate_ID, Cname) VALUES (8, 'H')
    
    drop table #Tracebility_T
    CREATE TABLE #Tracebility_T(
    	Tracebility_ID	INT,
    	TransactionCertificate_ID		INT,
    	LinkedTransactionCertificate_VC	varchar(20)
    )
    INSERT INTO #Tracebility_T(Tracebility_ID, TransactionCertificate_ID, LinkedTransactionCertificate_VC) VALUES (1, 1, 3)
    INSERT INTO #Tracebility_T(Tracebility_ID, TransactionCertificate_ID, LinkedTransactionCertificate_VC) VALUES (2, 1, 'zbc12')
    INSERT INTO #Tracebility_T(Tracebility_ID, TransactionCertificate_ID, LinkedTransactionCertificate_VC) VALUES (3, 3, 5)
    INSERT INTO #Tracebility_T(Tracebility_ID, TransactionCertificate_ID, LinkedTransactionCertificate_VC) VALUES (4, 4, 8)
    INSERT INTO #Tracebility_T(Tracebility_ID, TransactionCertificate_ID, LinkedTransactionCertificate_VC) VALUES (5, 1, 85)
    INSERT INTO #Tracebility_T(Tracebility_ID, TransactionCertificate_ID, LinkedTransactionCertificate_VC) VALUES (7, 2, 12)
    
    SELECT * from #TransactionCertificate_T
    SELECt * from #Tracebility_T
    
    ;WITH CTE AS
    (SELECT TOP 100 PERCENT
    	CAST(TransactionCertificate_ID as varchar(50)) AS TransactionCertificate_ID, 
    	#Tracebility_T.Tracebility_ID,
    	CAST(LinkedTransactionCertificate_VC as varchar(50) ) AS LinkedTransactionCertificate_VC, 
    	ROW_NUMBER() OVER ( PARTITION BY TransactionCertificate_ID ORDER BY Tracebility_ID desc ) AS Row
    FROM #Tracebility_T
    WHERE #Tracebility_T.LinkedTransactionCertificate_VC IS NOT NULL AND
    	NOT EXISTS (SELECT 1 FROM #Tracebility_T as T2 
    		WHERE CAST(#Tracebility_T.TransactionCertificate_ID as varchar(50)) = CAST(T2.LinkedTransactionCertificate_VC as varchar(50)) )
    	AND CAST(#Tracebility_T.TransactionCertificate_ID as varchar(50)) = @TransactionCertificate_ID
    
    UNION ALL
    SELECT 
    	CAST(CTE.LinkedTransactionCertificate_VC as varchar(50) ) AS LinkedTransactionCertificate_VC, 
    	#Tracebility_T.Tracebility_ID,
    	CAST(NULLIF(#Tracebility_T.LinkedTransactionCertificate_VC , CTE.LinkedTransactionCertificate_VC) as varchar(50)) AS LinkedTransactionCertificate_VC, 
    	CTE.Row
    FROM CTE
       , #Tracebility_T
     WHERE
      (    CAST(CTE.LinkedTransactionCertificate_VC as varchar(50) ) = CAST(#Tracebility_T.TransactionCertificate_ID as varchar(50) )
       OR  NOT EXISTS
          (SELECT 0
            FROM  #Tracebility_T as t2e
            WHERE CAST(CTE.LinkedTransactionCertificate_VC as varchar(50) ) = CAST(t2e.TransactionCertificate_ID as varchar(50))
          )
       AND CAST(CTE.LinkedTransactionCertificate_VC as varchar(50) ) = CAST(#Tracebility_T.LinkedTransactionCertificate_VC as varchar(50))
      )
    
    )
    
    select * from CTE
    I get an error
    Msg 240, Level 16, State 1, Line 35
    Types don't match between the anchor and the recursive part in column "TransactionCertificate_ID" of recursive query "CTE".


    Please help me.

    Thanks
    Last edited by Pat Phelan; 04-19-12 at 12:53. Reason: Changed Quote tags to Code tags

    --
    Shailesh Patil

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why CAST to varchar(50)?

    Why different names in first column of the anchor and the recursive part?

    INSERT INTO #Tracebility_T(Tracebility_ID, TransactionCertificate_ID, LinkedTransactionCertificate_VC) VALUES (2, 1, 'zbc12')
    Why string 'zbc12'? Why not integer?

    Please use [CODE] tag to keep format(e.g. leding blanks/fixed font).
    Last edited by tonkuma; 04-19-12 at 13:25. Reason: Add "Why string 'zbc12'? Why not integer?"

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can specify column names in parentheses after the expression_name, to avoid inconsistency, to simplify the query_definition, so on...
    Code:
    [ WITH <common_table_expression> [ ,...n ] ]
    
    <common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
        AS
        ( CTE_query_definition )
    WITH common_table_expression (Transact-SQL)

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CREATE TABLE #Tracebility_T(
    Tracebility_ID INT,
    TransactionCertificate_ID INT,
    LinkedTransactionCertificate_VC varchar(20)
    )
    Why varchar(20) for LinkedTransactionCertificate_VC?
    Why not declare "LinkedTransactionCertificate_ID INT" to keep consistency with "TransactionCertificate_ID INT"?

    Actually, you used numbers for "BasedOn", in your first example.
    ...
    I have 2 tables
    Tab1 Tab2
    CID Cname TabID CID BasedOn
    1 A 1 1 3
    2 B 2 1 4
    3 C 3 3 5
    4 D 4 4 8
    5 E 5
    6 F 6
    7 G 7
    8 H

    ...

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think you can specify multiple rows in an INSERT statement.
    Code:
    -- Standard INSERT syntax
    [ WITH <common_table_expression> [ ,...n ] ]
    INSERT 
    {
            [ TOP ( expression ) [ PERCENT ] ] 
            [ INTO ] 
            { <object> | rowset_function_limited 
              [ WITH ( <Table_Hint_Limited> [ ...n ] ) ] }
        {
            [ ( column_list ) ] 
            [ <OUTPUT Clause> ]
            { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ] 
              | derived_table 
              | execute_statement
              | <dml_table_source>
              | DEFAULT VALUES 
            }
        }
    }
    [; ]
    INSERT (Transact-SQL)


    For example:
    Code:
    INSERT INTO #TransactionCertificate_T
    VALUES
      ( 1 , 'A' )
    , ( 2 , 'B' )
    , ( 3 , 'C' )
    , ( 4 , 'D' )
    , ( 5 , 'E' )
    , ( 7 , 'G' )
    , ( 8 , 'H' )
    Last edited by tonkuma; 04-19-12 at 14:34. Reason: Add spaces in example. Add link to Transact-SQL onlune manual. Add an example.

  13. #13
    Join Date
    Aug 2007
    Posts
    11
    Hi Tonkuma

    I am sorry for my first post in which basedon was int column.
    The basedon can also be varchar unfortunately , as its an input column from another system, some other database on another server, which is not a part of existing database. We simply use this column (LinkedTransactionCertificate_VC)

    With the example provided in this post, works exactly the way it should. I implemented it in real situation and got the expected results but not with hierarchy of data.
    So i tries reproducing the same with same datatypes, then i got the error which i specified in my last post.

    Could you please let me know, what and where i am doing wrong.

    Million thanks to everybody for simplifying the task.

    Best regards

    --
    Shailesh Patil

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... got the expected results but not with hierarchy of data.
    Please publish sample data which include hierarchy(at least 4 or 5 levels would be better)
    and expeced results from the data.

    You used only #Tracebility_T in your last query.
    So, one "INSERT #Tracebility_T VALUES (...), (...), ..." might be enough to publish sample data,
    if my understanding of INSERT satement syntax was right.
    Last edited by tonkuma; 04-21-12 at 10:14.

  15. #15
    Join Date
    Aug 2007
    Posts
    11
    Quote Originally Posted by tonkuma View Post
    Please publish sample data which include hierarchy(at least 4 or 5 levels would be better)
    and expeced results from the data.

    You used only #Tracebility_T in your last query.
    So, one "INSERT #Tracebility_T VALUES (...), (...), ..." might be enough to publish sample data,
    if my understanding of INSERT satement syntax was right.
    For insert
    Code:
    INSERT INTO #TransactionCertificate_T
    VALUES
      ( 1 , 'A' )
    , ( 2 , 'B' )
    No It doesn't, it give me syntax error.
    Incorrect syntax near ','.

    --
    Shailesh Patil

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •