Post reply

TSQL Help, Possible a recursive CTE

  • July 12, 2024 at 2:51 pm

    #4435566

    Hi

    I have 2 tables

    Table A

    JoiningID, MainID

    1010619, 646703

    991840, 238649

    991840, 577578

    991840, 222063

    991840, 564824

    991840, 223543

    Table B

    MainID, JoiningID

    646703, 991840

    675515, 1010619

    These tables are obviously a lot bigger with lots of sets of IDs, this is just one example of where there is a relationship and the IDs can be grouped

    I need to produce table that lists all of the MainIDs that are grouped so something like

    GroupID, MainID

    I know its probably some recursive CTE but i dont seem to be able to work it out.

    Any help would be greatly appreciated.

    Thanks in advance

  • July 12, 2024 at 3:35 pm

    #4435583

    >6,000 points and can't be bothered giving us consumable data?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 12, 2024 at 3:39 pm

    #4435584

    Haha how do I do that?

    In an attachment?

  • July 12, 2024 at 3:49 pm

    #4435585

    Have a look at my second post in this thread, for example.

    https://www.sqlservercentral.com/forums/topic/sql-server-count-query

    • This reply was modified 5 months, 1 week ago by  Phil Parkin.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 12, 2024 at 3:52 pm

    #4435587

    Ok 👍

    As soon as I'm back at a pc I'll send it over

  • July 12, 2024 at 4:55 pm

    #4435593

    Here you are courtesy of ChatGPT

    -- Create TableA
    drop table if exists TableA 
    drop table if exists TableB 
    -- Create TableA
    CREATE TABLE TableA (
        JoiningID INT,
        MainID INT
    );

    -- Insert data into TableA
    INSERT INTO TableA (JoiningID, MainID) VALUES 
    (2867825, 757924),
    (1419610, 267753),
    (5614226, 930555),
    (5108603, 813536),
    (4744854, 542666),
    (8237956, 624535),
    (1934062, 576949),
    (9438974, 315169),
    (6781109, 448274),
    (1581462, 878145);

    -- Create TableB
    CREATE TABLE TableB (
        MainID INT,
        JoiningID INT
    );

    -- Insert data into TableB
    INSERT INTO TableB (MainID, JoiningID) VALUES 
    (573417, 2651177),
    (226882, 2016911),
    (359947, 7754864),
    (335612, 6692553),
    (167136, 2833230),
    (843245, 5467894),
    (947586, 6789345),
    (324678, 2345678),
    (785634, 1298764),
    (465123, 3456789);

    -- Create a temporary table to store the results
    CREATE TABLE #GroupResults (
        GroupID INT,
        MainID INT,
        JoiningID INT
    );

    -- Insert initial data from TableA
    INSERT INTO #GroupResults (GroupID, MainID, JoiningID)
    SELECT MainID, MainID, JoiningID
    FROM TableA;

    -- Variable to track changes
    DECLARE @RowsAffected INT = 1;

    -- Iterate to build the group relationships
    WHILE @RowsAffected > 0
    BEGIN
        -- Insert new relationships from TableA
        INSERT INTO #GroupResults (GroupID, MainID, JoiningID)
        SELECT r.GroupID, a.MainID, a.JoiningID
        FROM #GroupResults r
        JOIN TableA a ON r.JoiningID = a.JoiningID
        WHERE NOT EXISTS (
            SELECT 1
            FROM #GroupResults
            WHERE MainID = a.MainID AND GroupID = r.GroupID
        );

        -- Insert new relationships from TableB
        INSERT INTO #GroupResults (GroupID, MainID, JoiningID)
        SELECT r.GroupID, b.MainID, b.JoiningID
        FROM #GroupResults r
        JOIN TableB b ON r.JoiningID = b.JoiningID
        WHERE NOT EXISTS (
            SELECT 1
            FROM #GroupResults
            WHERE MainID = b.MainID AND GroupID = r.GroupID
        );

        -- Check how many rows were affected
        SET @RowsAffected = @@ROWCOUNT;
    END

    -- Select distinct GroupID and MainID to get the result
    SELECT DISTINCT
        GroupID,
        MainID
    FROM
        #GroupResults
    ORDER BY
        GroupID, MainID;

    -- Drop the temporary table
    DROP TABLE #GroupResults;

  • July 12, 2024 at 5:13 pm

    #4435620

    Jonathan AC Roberts wrote:

    Here you are courtesy of ChatGPT

    Heh... Ok... since you brought it up, what prompt(s) did you use?

    The key here is, again, the folks answering the questions shouldn't have to convert the data.  It should be "readily consumable".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 12, 2024 at 5:17 pm

    #4435621

    erics44 wrote:

    Haha how do I do that?

    In an attachment?

    Haha... Just like you've done before...

    https://www.sqlservercentral.com/forums/topic/hierachy-by-tsql#post-4093618

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 12, 2024 at 5:39 pm

    #4435629

    Thanks everyone for the light hearted replies

    I won't make this mistake again haha

  • July 12, 2024 at 7:41 pm

    #4435659

    Jeff Moden wrote:

    Jonathan AC Roberts wrote:

    Here you are courtesy of ChatGPT

    Heh... Ok... since you brought it up, what prompt(s) did you use?

    The key here is, again, the folks answering the questions shouldn't have to convert the data.  It should be "readily consumable".

    I had to use several, first I just pasted the question into ChatGPT. It came back with a recursive CTE but no DDL. So I asked it "can you generate some test data". And it came back with a grid showing rows. I then asked it "can you script it", it then came back with a  script that created TableA and TableB and populated them. I then pasted the table creation script and SQL to get results it had generated and it failed with:

    Msg 530, Level 16, State 1, Line 41
    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    So I pasted the error message into ChatGPT and it cam back with another script that generated the same error. So I asked it "I think there is something wrong with the query. I ran it as is and got:

    Msg 530, Level 16, State 1, Line 43

    The statement terminated. The maximum recursion 1000 has been exhausted before statement completion."

    It then came back with a script that produced no results so I asked it "no results".

    It then came back with the answer:

    Let's simplify the approach to ensure we get results. We will use a loop to iteratively update the groups until no more changes occur. Here is a revised script that should work without hitting recursion limits or producing no results:

    Revised SQL Script with Iterative Loop

    -- Create TableA
    CREATE TABLE TableA (
        JoiningID INT,
        MainID INT
    );

    -- Insert data into TableA
    INSERT INTO TableA (JoiningID, MainID) VALUES 
    (2867825, 757924),
    (1419610, 267753),
    (5614226, 930555),
    (5108603, 813536),
    (4744854, 542666),
    (8237956, 624535),
    (1934062, 576949),
    (9438974, 315169),
    (6781109, 448274),
    (1581462, 878145);

    -- Create TableB
    CREATE TABLE TableB (
        MainID INT,
        JoiningID INT
    );

    -- Insert data into TableB
    INSERT INTO TableB (MainID, JoiningID) VALUES 
    (573417, 2651177),
    (226882, 2016911),
    (359947, 7754864),
    (335612, 6692553),
    (167136, 2833230),
    (843245, 5467894),
    (947586, 6789345),
    (324678, 2345678),
    (785634, 1298764),
    (465123, 3456789);

    -- Create a temporary table to store the results
    CREATE TABLE #GroupResults (
        GroupID INT,
        MainID INT,
        JoiningID INT
    );

    -- Insert initial data from TableA
    INSERT INTO #GroupResults (GroupID, MainID, JoiningID)
    SELECT MainID, MainID, JoiningID
    FROM TableA;

    -- Variable to track changes
    DECLARE @RowsAffected INT = 1;

    -- Iterate to build the group relationships
    WHILE @RowsAffected > 0
    BEGIN
        -- Insert new relationships from TableA
        INSERT INTO #GroupResults (GroupID, MainID, JoiningID)
        SELECT r.GroupID, a.MainID, a.JoiningID
        FROM #GroupResults r
        JOIN TableA a ON r.JoiningID = a.JoiningID
        WHERE NOT EXISTS (
            SELECT 1
            FROM #GroupResults
            WHERE MainID = a.MainID AND GroupID = r.GroupID
        );

        -- Insert new relationships from TableB
        INSERT INTO #GroupResults (GroupID, MainID, JoiningID)
        SELECT r.GroupID, b.MainID, b.JoiningID
        FROM #GroupResults r
        JOIN TableB b ON r.JoiningID = b.JoiningID
        WHERE NOT EXISTS (
            SELECT 1
            FROM #GroupResults
            WHERE MainID = b.MainID AND GroupID = r.GroupID
        );

        -- Check how many rows were affected
        SET @RowsAffected = @@ROWCOUNT;
    END

    -- Select distinct GroupID and MainID to get the result
    SELECT DISTINCT
        GroupID,
        MainID
    FROM
        #GroupResults
    ORDER BY
        GroupID, MainID;

    -- Drop the temporary table
    DROP TABLE #GroupResults;

    Explanation:

    1. Create and insert into TableA and TableB: Sets up the tables with sample data.
    2. Create temporary table #GroupResults: Stores the results.
    3. Insert initial data from TableA: Seeds the temporary table with initial values.
    4. Iterative process: Continues to insert related MainIDs into the temporary table until no new relationships are found by tracking changes with @@ROWCOUNT.
    5. Select distinct results: Retrieves the grouped MainIDs.
    6. Drop the temporary table: Cleans up the temporary table.

    This script should correctly handle the relationships and produce results without recursion issues. If you encounter any issues or need further assistance, please let me know!

  • July 15, 2024 at 8:16 am

    #4436105

    The Chat GPT thing is interesting, its taking over the world

    I have added a spreadsheet with some data, if anyone could help it would be fantastic

    Attachments:
    You must be logged in to view attached files.

  • July 15, 2024 at 8:19 am

    #4436107

    I have combined the two tables so its just one table Id like to find the groupings of MainIDs joined by the joining IDs.

    so its like a zig zag joining, an example might be -

    start with a Main ID 1 has a Joining ID of 10

    Joining ID 10 has a main id of 2

    main id 2 has a joining ID of 20

    joining id 20 has a main id of 3

    main id 3 has a joining id of 30

    joining id 30 has a main id of 4

     

    etc

    • This reply was modified 5 months, 1 week ago by  erics44.

  • July 15, 2024 at 10:36 am

    #4436128

    erics44 wrote:

    The Chat GPT thing is interesting, its taking over the world

    I have added a spreadsheet with some data, if anyone could help it would be fantastic

    After all that coaching about how to provide consumable sample data, why have you posted a spreadsheet?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 15, 2024 at 10:54 am

    #4436131

    -- Create TableA

    CREATE TABLE TableA (

    MainID INT,

    JoiningID INT

    );

    -- Insert data into TableA

    INSERT INTO TableA (MainID, JoiningID) VALUES

    (605014, 964999),

    (605017, 965144),

    (605036, 965186),

    (605039, 965198),

    (605103, 965234),

    (605104, 965225),

    (605105, 965201),

    (605110, 965199),

    (605111, 965227),

    (605114, 965243),

    (605135, 965258),

    (605136, 965257),

    (605168, 965255),

    (605171, 965260),

    (605176, 965251),

    (605187, 965235),

    (605188, 965273),

    (605207, 965296),

    (605224, 965321),

    (605263, 965337),

    (605264, 965331),

    (605267, 965333),

    (605281, 965357),

    (605292, 965363),

    (605294, 965384),

    (605299, 965386),

    (605411, 965387),

    (605416, 965388),

    (605437, 965424),

    (605439, 965414),

    (605441, 965429),

    (605449, 965445),

    (605482, 965446),

    (605491, 965457),

    (605498, 965467),

    (605503, 965444),

    (605506, 965478),

    (605545, 965497),

    (605551, 965491),

    (605566, 965495),

    (605575, 965476),

    (605594, 965484),

    (605633, 965486),

    (605634, 965539),

    (605636, 965516),

    (605638, 965589),

    (605640, 965588),

    (605642, 965587),

    (605643, 965536),

    (605647, 965555),

    (605656, 965546),

    (605672, 965616),

    (605674, 965621),

    (605676, 965619),

    (605678, 965615),

    (605679, 965618),

    (605684, 965626),

    (605686, 965552),

    (605716, 965623),

    (605717, 965625),

    (605723, 965647),

    (605728, 965634),

    (605730, 965637),

    (605752, 965678),

    (605753, 965652),

    (605756, 965670),

    (605759, 965685),

    (605868, 965682),

    (605901, 965712),

    (605941, 965747),

    (605943, 965753),

    (605951, 965684),

    (605956, 965686),

    (605958, 965742),

    (605960, 965604),

    (605964, 965711),

    (605975, 965761),

    (605978, 965728),

    (605992, 965767),

    (605993, 965726),

    (605998, 965719),

    (605999, 965764),

    (606003, 965729),

    (606006, 965774),

    (606012, 965721),

    (606016, 965803),

    (606058, 965795),

    (606073, 965801),

    (606076, 965807),

    (606079, 965773),

    (606080, 965834),

    (606087, 965677),

    (606091, 965845),

    (606103, 965844),

    (606104, 965866),

    (606140, 965809),

    (606142, 965876),

    (606150, 965849),

    (606151, 965858),

    (606153, 965883),

    (198023, 965444),

    (541947, 965446),

    (535506, 965388),

    (567309, 965516),

    (247575, 965834),

    (540056, 965625),

    (566878, 965260),

    (605204, 965619),

    (587588, 965589),

    (537634, 965243),

    (248270, 965198),

    (241337, 965670),

    (207587, 965476),

    (221375, 965457),

    (565425, 965321),

    (566537, 965883),

    (586065, 965333),

    (565983, 965321),

    (550049, 965536),

    (597554, 965201),

    (505301, 965476),

    (536108, 965491),

    (604665, 965618),

    (514078, 965357),

    (247180, 965273),

    (213116, 965296),

    (525062, 965807),

    (238663, 965719),

    (604160, 965616),

    (590034, 965747),

    (540887, 965652),

    (521520, 965795),

    (541206, 965467),

    (596444, 965721),

    (235803, 965712),

    (604910, 965429),

    (592362, 965497),

    (556712, 965486),

    (516743, 965809),

    (174030, 965445),

    (535721, 965849),

    (583651, 965587),

    (587532, 965726),

    (247591, 965809),

    (517312, 965199),

    (536622, 965484),

    (571209, 965774),

    (246260, 965235),

    (585533, 965546),

    (587325, 965445),

    (214204, 965234),

    (604917, 965424),

    (251173, 965604),

    (552380, 965198),

    (591116, 965637),

    (500409, 965251),

    (587538, 965761),

    (239225, 965858),

    (561171, 965834),

    (548937, 965684),

    (535474, 965588),

    (602623, 965876),

    (239425, 965647),

    (596719, 965834),

    (537540, 965844),

    (545417, 965555),

    (245006, 965552),

    (251139, 965834),

    (240811, 965773),

    (597556, 965201),

    (583383, 965255),

    (587815, 965363),

    (583716, 965803),

    (565735, 965742),

    (584749, 965623),

    (247827, 965711),

    (577599, 965764),

    (250826, 965682),

    (586163, 965258),

    (524934, 965677),

    (519785, 965845),

    (532014, 965337),

    (516489, 965621),

    (542963, 965721),

    (540524, 965225),

    (244663, 965615),

    (526796, 965260),

    (519530, 965801),

    (600066, 965414),

    (565102, 965866),

    (585889, 965386),

    (244821, 965387),

    (587142, 965729),

    (558953, 965257),

    (602354, 965227),

    (585399, 965634),

    (183930, 965536),

    (248990, 965478),

    (216020, 965495),

    (596456, 965321),

    (554824, 965767),

    (532546, 965331),

    (572766, 965144),

    (250020, 965721),

    (533610, 965384),

    (553752, 965686),

    (245409, 965144),

    (551860, 965858),

    (526052, 965686),

    (582510, 965678),

    (198634, 965685),

    (249059, 964999),

    (520504, 965728),

    (502376, 965186),

    (605026, 965539),

    (596979, 965626),

    (590035, 965747),

    (571207, 965753);

    • This reply was modified 5 months, 1 week ago by  erics44.

  • July 15, 2024 at 10:55 am

    #4436132

    I think one of the issues with the data is that with so many rows there are lots of different problems to solve

    many main ids to one joiningid for example

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

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