Post reply

Extract related values recursively

  • June 11, 2012 at 11:58 pm

    #392492

    I have a table like this :

    salesinvoiceidsalesreceiptid

    aA1

    aB1

    bB1

    bC1

    cC1

    dD1

    At any point of time , I need to find the related invoices and payments for a particular invoice.

    E.g Lets say i want to get all the related invoices and Payments for Invoice "c".

    The first row I get is row 5 (c, C1).

    Then we find the receipt C1 is attached to invoice b as well . So we get that row (b,C1).

    Now , 2nd row has Invoice b which is attached to receipt B1, so get that for receipt B1 (b,B1)

    Again receipt B1 is attached to Invoice a , so again get that row (a,B1)

    One again , Invoice "a" is attached to receipt A1 , so get (a,A1).

    That's it as we dont have any other Invoice attached to A1. We stop here .

    So my results (i,e related invoice/payments for invoice "c) =

    (c,C1)

    (b,C1)

    (b,B1)

    (a,B1)

    (a,A1)

  • June 12, 2012 at 1:48 am

    This was removed by the editor as SPAM

  • June 12, 2012 at 2:45 am

    #1499654

    It doesn't work this way !

  • June 12, 2012 at 3:17 am

    This was removed by the editor as SPAM

  • June 12, 2012 at 7:02 am

    #1499768

    Here is the recursive solution but I'm not sure why you'd want to use it.

    DECLARE @s-2 TABLE (salesinvoiceid CHAR(1), salesreceiptid CHAR(2))

    INSERT INTO @s-2

    SELECT 'a','A1' UNION ALL SELECT 'a','B1' UNION ALL SELECT 'b','B1'

    UNION ALL SELECT 'b','C1' UNION ALL SELECT 'c','C1' UNION ALL SELECT 'd','D1'

    DECLARE @StartInv CHAR(1) = 'c'

    ;WITH Inv AS (

    SELECT s2.salesinvoiceid, s2.salesreceiptid, n=1, rid=s2.salesreceiptid, iid=s2.salesinvoiceid

    FROM @s-2 s1

    INNER JOIN @s-2 s2 ON s1.salesreceiptid = s2.salesreceiptid

    WHERE s1.salesinvoiceid = @StartInv

    UNION ALL

    SELECT s2.salesinvoiceid, s2.salesreceiptid, n+1, s2.salesreceiptid, s2.salesinvoiceid

    FROM Inv s1

    INNER JOIN @s-2 s2 ON s1.salesinvoiceid = s2.salesinvoiceid

    WHERE n%2 = 1 AND rid <> s2.salesreceiptid

    UNION ALL

    SELECT s2.salesinvoiceid, s2.salesreceiptid, n+1, s2.salesreceiptid, s2.salesinvoiceid

    FROM Inv s1

    INNER JOIN @s-2 s2 ON s1.salesreceiptid = s2.salesreceiptid

    WHERE n%2 = 0 AND iid <> s2.salesinvoiceid

    )

    SELECT salesinvoiceid, salesreceiptid

    FROM Inv

    -- Expected results:

    --(c,C1)

    --(b,C1)

    --(b,B1)

    --(a,B1)

    --(a,A1)

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • June 13, 2012 at 3:15 am

    #1500221

    Hi ,

    Thanks , it does give the right results .

    However, just to understand the code , what does the n%2 in the Where condition signify ?

    thanks

  • June 13, 2012 at 4:25 am

    #1500245

    sridhar_kola (6/13/2012)

    Hi ,

    Thanks , it does give the right results .

    However, just to understand the code , what does the n%2 in the Where condition signify ?

    thanks

    I use the %2 on the pass counter (n) to determine which JOIN ON to use. Odd passes use one, even passes use the other.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • June 13, 2012 at 6:09 am

    #1500295

    dwain.c (6/13/2012)

    sridhar_kola (6/13/2012)

    Hi ,

    Thanks , it does give the right results .

    However, just to understand the code , what does the n%2 in the Where condition signify ?

    thanks

    I use the %2 on the pass counter (n) to determine which JOIN ON to use. Odd passes use one, even passes use the other.

    It might be a little easier to understand with a conditional join rather than a conditional on one of two recursive parts:

    ;WITH Resolver AS (

    SELECT

    salesinvoiceid,

    salesreceiptid,

    [Level] = 0

    FROM @s-2

    WHERE salesinvoiceid = @StartInv

    UNION ALL

    SELECT

    tr.salesinvoiceid,

    tr.salesreceiptid,

    [Level] = lr.[Level]+1

    FROM Resolver lr

    INNER JOIN @s-2 tr

    ON (lr.[Level]%2 = 0 AND tr.salesreceiptid = lr.salesreceiptid AND tr.salesinvoiceid <> lr.salesinvoiceid)

    OR (lr.[Level]%2 = 1 AND tr.salesinvoiceid = lr.salesinvoiceid AND tr.salesreceiptid <> lr.salesreceiptid)

    )

    SELECT * FROM Resolver

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 13, 2012 at 6:13 am

    #1500299

    ChrisM@Work (6/13/2012)

    dwain.c (6/13/2012)

    sridhar_kola (6/13/2012)

    Hi ,

    Thanks , it does give the right results .

    However, just to understand the code , what does the n%2 in the Where condition signify ?

    thanks

    I use the %2 on the pass counter (n) to determine which JOIN ON to use. Odd passes use one, even passes use the other.

    It might be a little easier to understand with a conditional join rather than a conditional on one of two recursive parts:

    Maybe! But the two part recursive leg was just too much fun to resist! 😛

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply