July 12, 2024 at 2:51 pm
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
>6,000 points and can't be bothered giving us consumable data?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 12, 2024 at 3:39 pm
Haha how do I do that?
In an attachment?
July 12, 2024 at 3:49 pm
Have a look at my second post in this thread, for example.
https://www.sqlservercentral.com/forums/topic/sql-server-count-query
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 12, 2024 at 3:52 pm
Ok 👍
As soon as I'm back at a pc I'll send it over
July 12, 2024 at 4:55 pm
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
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
Change is inevitable... Change for the better is not.
July 12, 2024 at 5:17 pm
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
Change is inevitable... Change for the better is not.
July 12, 2024 at 5:39 pm
Thanks everyone for the light hearted replies
I won't make this mistake again haha
July 12, 2024 at 7:41 pm
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:
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:19 am
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
July 15, 2024 at 10:36 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 15, 2024 at 10:54 am
-- 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);
July 15, 2024 at 10:55 am
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