Post reply

self join in loop in sqlserver

  • June 23, 2010 at 9:34 am

    #222531

    need help please

    this is source

    new proj id old proj id

    1077a 1077/

    1077b 1077a

    1077c 1077b

    2134a 2134/

    2134b 2134a

    2134c 2134b

    and we need to get target table as

    new proj id old proj id

    1077c 1077/

    1077c 1077a

    1077c 1077b

    1077c 1077c

    2134c 2134/

    2134c 2134a

    2134c 2134b

    2134c 2134c

  • June 23, 2010 at 10:14 am

    #1184060

    How's this:

    -- See how this starts off with a table and data in it?

    -- If you had provided us the data in this format,

    -- it would have made things easier for all of the

    -- volunteers on this site to help you out.

    declare @test-2 table ([new proj id] char(5), [old proj id] char(5))

    insert into @test-2

    SELECT '1077a', '1077/' UNION ALL

    SELECT '1077b', '1077a' UNION ALL

    SELECT '1077c', '1077b' UNION ALL

    SELECT '2134a', '2134/' UNION ALL

    SELECT '2134b', '2134a' UNION ALL

    SELECT '2134c', '2134b'

    ;WITH CTE AS

    (

    SELECT t1.[new proj id], [old proj id] = t1.[new proj id]

    FROM @test-2 t1

    LEFT JOIN @test-2 t2

    ON t1.[new proj id] = t2.[old proj id]

    WHERE t2.[old proj id] IS NULL

    )

    SELECT *

    FROM CTE

    UNION ALL

    SELECT CTE.[new proj id], t1.[old proj id]

    FROM @test-2 t1

    JOIN CTE

    ON LEFT(t1.[old proj id],4) = LEFT(cte.[new proj id],4)

    ORDER BY [new proj id], [old proj id]

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 23, 2010 at 10:55 am

    #1184094

    Hi Wayne

    thanks a lot its working gud really appriciate it

  • June 23, 2010 at 10:56 am

    #1184095

    also can you please help me to understand the code very briefly

    thanks

  • June 23, 2010 at 11:45 am

    #1184117

    Rahul

    Wayne has appliced good logiv here...Coming to the explanation of this code first of all code is creating a table variable named @test-2 and populate it with the data as per your requirement.

    You have below data...

    New PID Old PID

    1077a1077/

    1077b1077a

    1077c1077b

    2134a2134/

    2134b2134a

    2134c2134b

    Only 1077c and 2134c does not present in OldProjectID column...so If you make a Inner Join those are the only values which can not find match on OldPID column...So by using this mis match values

    Code is preparint Common Table Expresion (CTE) with LEFT OUTER JOIN and as OldProjectID column values it's setting the NewProjectID and code is making this LEFT OUTER JOIN where t2.OldProjectID IS NULL which is possible only if your NewPID is 1077c and 2134c so your CTE will have following format

    NewPID OldPID

    1077c1077c

    2134c2134c

    and then Code simply joins these two tables with UnionAll and Inner JOIN wehre Code is using LEFT

    function and so it can make join only on Numeric Values from Alpha Numeric string...

    If you are not clear just use temp table or Table Variable instead of CTE and debug the code in multiple parts so you can see which values CTE in holding...You can run following and check it...

    -- See how this starts off with a table and data in it?

    -- If you had provided us the data in this format,

    -- it would have made things easier for all of the

    -- volunteers on this site to help you out.

    declare @test-2 table ([new proj id] char(5), [old proj id] char(5))

    insert into @test-2

    SELECT '1077a', '1077/' UNION ALL

    SELECT '1077b', '1077a' UNION ALL

    SELECT '1077c', '1077b' UNION ALL

    SELECT '2134a', '2134/' UNION ALL

    SELECT '2134b', '2134a' UNION ALL

    SELECT '2134c', '2134b'

    --See the @test-2 Table Variable after loading with data

    select * from @test-2

    --Populating #Temp table

    SELECT t1.[new proj id], [old proj id] = t1.[new proj id] into #temp

    FROM @test-2 t1

    LEFT JOIN @test-2 t2

    ON t1.[new proj id] = t2.[old proj id]

    WHERE t2.[old proj id] IS NULL

    --Check which Value #Temp is Holding.

    --In the solution Wayne suggested same values will be stored in CTE.

    select * from #temp

    SELECT *

    FROM #temp

    UNION ALL

    SELECT t2.[new proj id], t1.[old proj id]

    FROM @test-2 t1

    JOIN #temp t2

    ON LEFT(t1.[old proj id],4) = LEFT(t2.[new proj id],4)

    ORDER BY [new proj id], [old proj id]

    drop table #temp

    FO

  • June 23, 2010 at 12:34 pm

    #1184148

    thanks falgunoza this helps my need

  • June 23, 2010 at 12:45 pm

    #1184154

    rahulsony111 (6/23/2010)

    also can you please help me to understand the code very briefly

    thanks

    Rahul, falgunoza did a pretty good job of explaining it. If there is something that you still don't understand, speak up and I'll expain it and my thought process.

    In my opinion, you should never use something that you don't understand... if you can't explain it so someone else, then you shouldn't be blindly using it.

    @falgunoza - thanks!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 23, 2010 at 1:09 pm

    #1184161

    hi Wayne, falgunoza

    here with out useng the left keyword can we get the output because there are some cases below is the eg

    below source table:

    new proj id old proj id

    1084d 1084/

    1084e 1084a

    1084f 1084b

    1077a 1077/

    1077b 1077a

    2134a 2134/

    2134b 2134a

    and the desired target is:

    new proj id old proj id

    1077b 1077/

    1077b 1077a

    1077b 1077b

    2134b 2134/

    2134b 2134a

    2134b 2134b

    1084d 1084/

    1084e 1084a

    1084f 1084b

  • June 23, 2010 at 2:29 pm

    #1184200

    or we can apply if else logic or while in CTE so that if old proj id is not equal to new proj id then it should not do anything jus print it as it is

  • June 23, 2010 at 2:33 pm

    #1184202

    using if else or while in cte we can achive this target i think need some help on this pls

    new proj id old proj id

    1084d 1084/

    1084e 1084a

    1084f 1084b

    1077a 1077/

    1077b 1077a

    2134a 2134/

    2134b 2134a

    and the desired target is:

    new proj id old proj id

    1077b 1077/

    1077b 1077a

    2134b 2134/

    2134b 2134a

    1084d 1084/

    1084e 1084a

    1084f 1084b

  • June 23, 2010 at 3:51 pm

    #1184232

    assuming that you would also need rows in the output of:

    1084d1084d

    1084e1084e

    1084f1084f

    Then this would work. Someone might be able to make it nicer...

    -- See how this starts off with a table and data in it?

    -- If you had provided us the data in this format,

    -- it would have made things easier for all of the

    -- volunteers on this site to help you out.

    declare @test-2 table (RowID int IDENTITY, [new proj id] char(5), [old proj id] char(5))

    insert into @test-2

    SELECT '1077a', '1077/' UNION ALL

    SELECT '1077b', '1077a' UNION ALL

    SELECT '1077c', '1077b' UNION ALL

    SELECT '2134a', '2134/' UNION ALL

    SELECT '2134b', '2134a' UNION ALL

    SELECT '2134c', '2134b' UNION ALL

    SELECT '1084d', '1084/' UNION ALL

    SELECT '1084e', '1084a' UNION ALL

    SELECT '1084f', '1084b'

    DECLARE @test2 TABLE ([proj_id] char(5) PRIMARY KEY CLUSTERED, Parent int, Child int, RN int)

    INSERT INTO @test2 (proj_id, Parent, Child)

    SELECT [old proj id], 0, 0

    FROM @test-2

    UNION

    SELECT [new proj id], 0, 0

    FROM @test-2

    UPDATE t1

    SET Child = RowID,

    RN = RowID

    FROM @test2 t1

    JOIN @test-2 t2

    ON t1.proj_id = t2.[old proj id]

    UPDATE t1

    SET Parent = RowID,

    RN = RowID

    FROM @test2 t1

    JOIN @test-2 t2

    ON t1.proj_id = t2.[new proj id]

    WHERE t1.RN IS NULL

    UPDATE t

    SET Parent = (SELECT MIN(RN) FROM @test2 WHERE Parent >= t.Child)

    FROM @test2 t

    WHERE Parent = 0

    SELECT [new proj id] = t2.proj_id,

    [old proj id] = t1.proj_id

    FROM @test2 t1

    JOIN @test2 t2

    ON t1.Parent = t2.Parent

    AND t2.Child = 0

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 23, 2010 at 8:07 pm

    #1184291

    Thanks Wayne, as i cant access the server after 5, so thought of reposting the question clearly with other cases and so did it,

    sorry for that

  • June 24, 2010 at 1:21 pm

    #1184670

    thanks wayne the code is pretty much worked but i found a small problem

    here is the source with row (New_proj_Id char(5), Old_proj_Id char(5), row_id)

    SCAS1156C ,SCAS1156/ ,39

    SCAS1156D ,SCAS1156B ,40

    SCAS1156E ,SCAS1156C ,41

    after i executed your code the code was working gud with rest of all cases which we discussed exept this

    here i am pasting the result from the code

    SCAS1156D,SCAS1156/

    SCAS1156D,SCAS1156B

    SCAS1156E,SCAS1156C

    SCAS1156D,SCAS1156D

    SCAS1156E,SCAS1156E

    but the desired target should be

    SCAS1156E,SCAS1156/

    SCAS1156D,SCAS1156B

    SCAS1156E,SCAS1156C

    SCAS1156D,SCAS1156D

    SCAS1156E,SCAS1156E

    please let me know

    thanks you

  • June 24, 2010 at 1:41 pm

    #1184680

    I'm not going to reformat your data into usable code again. I you want me to look at it, then you will need to at least put the new data to check into statements like this:

    SELECT '1077a', '1077/' UNION ALL

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 24, 2010 at 1:54 pm

    #1184686

    Rahul

    Is this the sample data or this is the only data you have ?

    FO

Viewing 15 posts - 1 through 15 (of 22 total)

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