July 20, 2006 at 7:01 am
SET NOCOUNT ON
DECLARE @Relations TABLE
(
Main VARCHAR(10),
Related VARCHAR(10)
)
INSERT @Relations
SELECT 'AA', 'BB' UNION
SELECT 'AA', 'CC' UNION
SELECT 'AA', 'DD' UNION
SELECT 'BB', 'EE' UNION
SELECT 'MM', 'NN' UNION
SELECT 'SS', 'MM' UNION
SELECT 'WW', 'XX' UNION
SELECT 'XX', 'YY'
/*
I have a relations table as above. There can be relationships multiple levels. Main can be realed and related can be main. Based on the above data I need the following results.
RelationGroup can be like sequenial generated.
RelationGroup Name
1 AA
1 BB
1 CC
1 DD
1 EE
2 MM
2 NN
2 SS
3 WW
3 XX
3 YY
I Did it with a cursor. Is there a way to do it with set based approach.
*/
Regards,
gova
July 20, 2006 at 7:49 am
SET NOCOUNT ON
DECLARE @Relations TABLE
(Main VARCHAR(10),Related VARCHAR(10))
INSERT @Relations
SELECT 'AA', 'BB' UNION
SELECT 'AA', 'CC' UNION
SELECT 'AA', 'DD' UNION
SELECT 'BB', 'EE' UNION
SELECT 'MM', 'NN' UNION
SELECT 'SS', 'MM' UNION
SELECT 'WW', 'XX' UNION
SELECT 'XX', 'YY' UNION
SELECT 'YY', 'TT' UNION
SELECT 'TT', 'LL'
SELECT * FROM @Relations
WHILE @@rowcount>0
BEGIN
UPDATE A
SET A.Main=B.Main
FROM @Relations A INNER JOIN @Relations B ON
A.Main=B.Related
END
SELECT * FROM @Relations
/*
--add the ROOT nodes to relative column
UNION ALL
SELECT a.Main,a.Main FROM
@Relations A LEFT OUTER JOIN @Relations B
ON A.Main=B.Related
WHERE B.Related IS NULL
GROUP BY a.Main
*/
Vasc
July 20, 2006 at 8:29 am
Thanks Vasc. I miss one row in each group still. Actual reqirement is group all related with a new generated id as below.
RelationGroup Name
1 AA
1 BB
1 CC
1 DD
1 EE
2 MM
2 NN
2 SS
3 WW
3 XX
3 YY
My mistake. I did not notice the lines in your comment. Thanks a lot.
Regards,
gova
July 20, 2006 at 9:07 am
Hi Vasc it doesn't work in large relationships table. Try with following data. It goes in an endless loop.
SET NOCOUNT ON
CREATE TABLE #Relations
(Main VARCHAR(10),Related VARCHAR(10))
INSERT #Relations
SELECT '100', '465' UNION
SELECT '101', '104' UNION
SELECT '102', '258' UNION
SELECT '102', '286' UNION
SELECT '103', '279' UNION
SELECT '103', '285' UNION
SELECT '104', '101' UNION
SELECT '105', '106' UNION
SELECT '106', '105' UNION
SELECT '107', '118' UNION
SELECT '107', '405' UNION
SELECT '107', '558' UNION
SELECT '108', '116' UNION
SELECT '108', '414' UNION
SELECT '109', '349' UNION
SELECT '111', '652' UNION
SELECT '111', '653' UNION
SELECT '112', '139' UNION
SELECT '113', '319' UNION
SELECT '113', '327' UNION
SELECT '114', '326' UNION
SELECT '114', '332' UNION
SELECT '116', '108' UNION
SELECT '117', '270' UNION
SELECT '118', '107' UNION
SELECT '119', '357' UNION
SELECT '120', '142' UNION
SELECT '120', '152' UNION
SELECT '121', '274' UNION
SELECT '122', '123' UNION
SELECT '122', '378' UNION
SELECT '124', '247' UNION
SELECT '126', '125' UNION
SELECT '128', '127' UNION
SELECT '129', '265' UNION
SELECT '129', '277' UNION
SELECT '130', '267' UNION
SELECT '130', '278' UNION
SELECT '131', '372' UNION
SELECT '132', '365' UNION
SELECT '134', '304' UNION
SELECT '135', '143' UNION
SELECT '136', '404' UNION
SELECT '138', '395' UNION
SELECT '139', '112' UNION
SELECT '145', '595' UNION
SELECT '146', '211' UNION
SELECT '147', '591' UNION
SELECT '149', '500' UNION
SELECT '150', '148' UNION
SELECT '151', '155' UNION
SELECT '151', '156' UNION
SELECT '154', '466' UNION
SELECT '157', '514' UNION
SELECT '159', '110' UNION
SELECT '160', '162' UNION
SELECT '162', '160' UNION
SELECT '166', '172' UNION
SELECT '167', '168' UNION
SELECT '169', '424' UNION
SELECT '169', '426' UNION
SELECT '170', '427' UNION
SELECT '170', '428' UNION
SELECT '171', '186' UNION
SELECT '175', '183' UNION
SELECT '179', '173' UNION
SELECT '180', '185' UNION
SELECT '181', '177' UNION
SELECT '182', '174' UNION
SELECT '184', '178' UNION
SELECT '187', '161' UNION
SELECT '188', '165' UNION
SELECT '189', '343' UNION
SELECT '190', '402' UNION
SELECT '195', '192' UNION
SELECT '195', '193' UNION
SELECT '195', '194' UNION
SELECT '197', '309' UNION
SELECT '200', '206' UNION
SELECT '201', '176' UNION
SELECT '201', '198' UNION
SELECT '201', '199' UNION
SELECT '202', '308' UNION
SELECT '205', '429' UNION
SELECT '207', '196' UNION
SELECT '208', '204' UNION
SELECT '209', '509' UNION
SELECT '209', '510' UNION
SELECT '210', '508' UNION
SELECT '211', '146' UNION
SELECT '213', '512' UNION
SELECT '214', '383' UNION
SELECT '215', '212' UNION
SELECT '216', '389' UNION
SELECT '217', '368' UNION
SELECT '217', '369' UNION
SELECT '223', '222' UNION
SELECT '224', '221' UNION
SELECT '225', '144' UNION
SELECT '227', '228' UNION
SELECT '228', '227' UNION
SELECT '229', '230' UNION
SELECT '229', '231' UNION
SELECT '229', '232' UNION
SELECT '233', '218' UNION
SELECT '233', '219' UNION
SELECT '233', '220' UNION
SELECT '234', '493' UNION
SELECT '234', '494' UNION
SELECT '235', '478' UNION
SELECT '236', '158' UNION
SELECT '237', '163' UNION
SELECT '238', '439' UNION
SELECT '238', '440' UNION
SELECT '238', '453' UNION
SELECT '239', '394' UNION
SELECT '240', '164' UNION
SELECT '242', '241' UNION
SELECT '243', '345' UNION
SELECT '244', '245' UNION
SELECT '245', '244' UNION
SELECT '246', '297' UNION
SELECT '246', '354' UNION
SELECT '246', '382' UNION
SELECT '247', '124' UNION
SELECT '249', '489' UNION
SELECT '250', '253' UNION
SELECT '251', '386' UNION
SELECT '251', '387' UNION
SELECT '252', '462' UNION
SELECT '252', '464' UNION
SELECT '252', '550' UNION
SELECT '252', '551' UNION
SELECT '255', '260' UNION
SELECT '256', '248' UNION
SELECT '257', '415' UNION
SELECT '261', '203' UNION
SELECT '262', '264' UNION
SELECT '264', '262' UNION
SELECT '266', '573' UNION
SELECT '268', '306' UNION
SELECT '269', '407' UNION
SELECT '272', '263' UNION
SELECT '272', '273' UNION
SELECT '272', '292' UNION
SELECT '275', '283' UNION
SELECT '276', '401' UNION
SELECT '279', '103' UNION
SELECT '280', '322' UNION
SELECT '281', '457' UNION
SELECT '281', '458' UNION
SELECT '282', '437' UNION
SELECT '282', '438' UNION
SELECT '287', '333' UNION
SELECT '287', '339' UNION
SELECT '287', '409' UNION
SELECT '287', '418' UNION
SELECT '288', '390' UNION
SELECT '289', '290' UNION
SELECT '291', '331' UNION
SELECT '293', '329' UNION
SELECT '298', '463' UNION
SELECT '299', '294' UNION
SELECT '299', '295' UNION
SELECT '299', '296' UNION
SELECT '301', '306' UNION
SELECT '302', '416' UNION
SELECT '303', '410' UNION
SELECT '305', '513' UNION
SELECT '306', '268' UNION
SELECT '306', '300' UNION
SELECT '306', '301' UNION
SELECT '307', '137' UNION
SELECT '310', '345' UNION
SELECT '311', '499' UNION
SELECT '312', '336' UNION
SELECT '313', '582' UNION
SELECT '314', '444' UNION
SELECT '315', '254' UNION
SELECT '316', '459' UNION
SELECT '317', '340' UNION
SELECT '321', '583' UNION
SELECT '323', '318' UNION
SELECT '324', '468' UNION
SELECT '325', '363' UNION
SELECT '337', '338' UNION
SELECT '343', '189' UNION
SELECT '344', '355' UNION
SELECT '346', '342' UNION
SELECT '347', '398' UNION
SELECT '347', '497' UNION
SELECT '347', '569' UNION
SELECT '348', '284' UNION
SELECT '349', '546' UNION
SELECT '349', '547' UNION
SELECT '349', '552' UNION
SELECT '349', '553' UNION
SELECT '350', '371' UNION
SELECT '357', '542' UNION
SELECT '357', '543' UNION
SELECT '357', '544' UNION
SELECT '360', '330' UNION
SELECT '360', '341' UNION
SELECT '360', '359' UNION
SELECT '361', '334' UNION
SELECT '361', '335' UNION
SELECT '361', '358' UNION
SELECT '362', '351' UNION
SELECT '362', '352' UNION
SELECT '362', '356' UNION
SELECT '364', '320' UNION
SELECT '364', '328' UNION
SELECT '364', '353' UNION
SELECT '366', '133' UNION
SELECT '370', '473' UNION
SELECT '375', '367' UNION
SELECT '375', '373' UNION
SELECT '376', '534' UNION
SELECT '376', '535' UNION
SELECT '376', '536' UNION
SELECT '376', '537' UNION
SELECT '377', '374' UNION
SELECT '377', '565' UNION
SELECT '379', '534' UNION
SELECT '379', '535' UNION
SELECT '379', '536' UNION
SELECT '379', '537' UNION
SELECT '384', '502' UNION
SELECT '388', '271' UNION
SELECT '392', '397' UNION
SELECT '393', '400' UNION
SELECT '396', '381' UNION
SELECT '399', '380' UNION
SELECT '403', '385' UNION
SELECT '405', '107' UNION
SELECT '406', '226' UNION
SELECT '408', '576' UNION
SELECT '411', '121' UNION
SELECT '411', '274' UNION
SELECT '412', '413' UNION
SELECT '414', '108' UNION
SELECT '417', '577' UNION
SELECT '419', '117' UNION
SELECT '420', '391' UNION
SELECT '421', '191' UNION
SELECT '422', '452' UNION
SELECT '425', '460' UNION
SELECT '430', '432' UNION
SELECT '433', '482' UNION
SELECT '434', '433' UNION
SELECT '434', '481' UNION
SELECT '434', '482' UNION
SELECT '435', '259' UNION
SELECT '436', '568' UNION
SELECT '439', '238' UNION
SELECT '442', '443' UNION
SELECT '443', '442' UNION
SELECT '445', '446' UNION
SELECT '447', '448' UNION
SELECT '449', '567' UNION
SELECT '450', '451' UNION
SELECT '451', '450' UNION
SELECT '454', '455' UNION
SELECT '456', '566' UNION
SELECT '461', '441' UNION
SELECT '465', '100' UNION
SELECT '466', '154' UNION
SELECT '467', '533' UNION
SELECT '469', '474' UNION
SELECT '470', '471' UNION
SELECT '470', '475' UNION
SELECT '470', '476' UNION
SELECT '471', '470' UNION
SELECT '471', '475' UNION
SELECT '471', '476' UNION
SELECT '472', '496' UNION
SELECT '474', '469' UNION
SELECT '475', '470' UNION
SELECT '475', '471' UNION
SELECT '475', '476' UNION
SELECT '476', '470' UNION
SELECT '476', '471' UNION
SELECT '476', '475' UNION
SELECT '477', '498' UNION
SELECT '479', '480' UNION
SELECT '480', '479' UNION
SELECT '481', '482' UNION
SELECT '482', '433' UNION
SELECT '482', '434' UNION
SELECT '482', '481' UNION
SELECT '483', '484' UNION
SELECT '485', '431' UNION
SELECT '485', '486' UNION
SELECT '485', '503' UNION
SELECT '485', '504' UNION
SELECT '486', '485' UNION
SELECT '487', '488' UNION
SELECT '488', '487' UNION
SELECT '491', '490' UNION
SELECT '495', '140' UNION
SELECT '496', '472' UNION
SELECT '498', '477' UNION
SELECT '501', '141' UNION
SELECT '503', '504' UNION
SELECT '504', '503' UNION
SELECT '505', '431' UNION
SELECT '505', '485' UNION
SELECT '505', '486' UNION
SELECT '505', '503' UNION
SELECT '505', '504' UNION
SELECT '505', '506' UNION
SELECT '506', '505' UNION
SELECT '507', '115' UNION
SELECT '511', '492' UNION
SELECT '515', '516' UNION
SELECT '517', '518' UNION
SELECT '518', '517' UNION
SELECT '519', '520' UNION
SELECT '519', '521' UNION
SELECT '519', '522' UNION
SELECT '519', '523' UNION
SELECT '519', '524' UNION
SELECT '529', '525' UNION
SELECT '529', '526' UNION
SELECT '529', '527' UNION
SELECT '529', '528' UNION
SELECT '529', '530' UNION
SELECT '531', '570' UNION
SELECT '531', '571' UNION
SELECT '531', '572' UNION
SELECT '533', '532' UNION
SELECT '540', '575' UNION
SELECT '541', '538' UNION
SELECT '545', '556' UNION
SELECT '545', '557' UNION
SELECT '548', '539' UNION
SELECT '548', '549' UNION
SELECT '554', '555' UNION
SELECT '559', '560' UNION
SELECT '559', '561' UNION
SELECT '559', '562' UNION
SELECT '559', '563' UNION
SELECT '573', '574' UNION
SELECT '578', '579' UNION
SELECT '578', '664' UNION
SELECT '579', '578' UNION
SELECT '579', '663' UNION
SELECT '580', '564' UNION
SELECT '581', '423' UNION
SELECT '582', '313' UNION
SELECT '583', '321' UNION
SELECT '585', '587' UNION
SELECT '585', '593' UNION
SELECT '586', '588' UNION
SELECT '587', '585' UNION
SELECT '588', '586' UNION
SELECT '588', '596' UNION
SELECT '589', '590' UNION
SELECT '591', '147' UNION
SELECT '592', '651' UNION
SELECT '592', '666' UNION
SELECT '593', '585' UNION
SELECT '594', '584' UNION
SELECT '596', '588' UNION
SELECT '597', '661' UNION
SELECT '599', '602' UNION
SELECT '603', '600' UNION
SELECT '604', '601' UNION
SELECT '605', '608' UNION
SELECT '605', '611' UNION
SELECT '606', '669' UNION
SELECT '608', '605' UNION
SELECT '608', '611' UNION
SELECT '609', '607' UNION
SELECT '610', '613' UNION
SELECT '611', '605' UNION
SELECT '611', '608' UNION
SELECT '612', '630' UNION
SELECT '613', '610' UNION
SELECT '614', '615' UNION
SELECT '615', '614' UNION
SELECT '617', '616' UNION
SELECT '618', '620' UNION
SELECT '619', '623' UNION
SELECT '621', '622' UNION
SELECT '624', '655' UNION
SELECT '624', '656' UNION
SELECT '624', '657' UNION
SELECT '627', '626' UNION
SELECT '628', '626' UNION
SELECT '629', '626' UNION
SELECT '630', '612' UNION
SELECT '631', '626' UNION
SELECT '632', '633' UNION
SELECT '633', '632' UNION
SELECT '634', '635' UNION
SELECT '635', '634' UNION
SELECT '637', '636' UNION
SELECT '638', '639' UNION
SELECT '640', '641' UNION
SELECT '641', '640' UNION
SELECT '642', '598' UNION
SELECT '643', '625' UNION
SELECT '644', '645' UNION
SELECT '645', '644' UNION
SELECT '646', '665' UNION
SELECT '647', '660' UNION
SELECT '648', '654' UNION
SELECT '649', '655' UNION
SELECT '649', '656' UNION
SELECT '649', '657' UNION
SELECT '650', '659' UNION
SELECT '661', '597' UNION
SELECT '662', '658' UNION
SELECT '668', '667' UNION
SELECT '670', '153' UNION
SELECT '671', '672'
/*
UPDATE A
SET A.Main=B.Main
FROM #Relations A INNER JOIN #Relations B ON
A.Main=B.Related
SELECT @@ROWCOUNT
*/
WHILE @@rowcount>0
BEGIN
UPDATE A
SET A.Main=B.Main
FROM #Relations A INNER JOIN #Relations B ON
A.Main=B.Related
END
SELECT * FROM #Relations
UNION
SELECT a.Main,a.Main FROM
#Relations A LEFT OUTER JOIN #Relations B
ON A.Main=B.Related
WHERE B.Related IS NULL
GROUP BY a.Main
ORDER BY 1
Regards,
gova
July 20, 2006 at 12:18 pm
are this ones possible??????
SELECT '644', '645' UNION
SELECT '645', '644' UNION
118 107
405 107
107 118
I thought you have an tree ,,,
Vasc
July 20, 2006 at 1:13 pm
/*
Unfortunately yes. It is circular relations.
That is how the datafeeds are. The speck is for this case
118, 107, 405 are related to each other with a generated ID and
645, 644 are related to each other with generated id.
I did it like this with cursor. I would be happy if there is SET based way to this.
*/
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#Relations') IS NOT NULL
DROP TABLE #Relations
IF OBJECT_ID('tempdb.dbo.#NewRelations') IS NOT NULL
DROP TABLE #NewRelations
CREATE TABLE #Relations
(Main VARCHAR(10),Related VARCHAR(10))
INSERT #Relations
SELECT '100', '465' UNION
SELECT '101', '104' UNION
SELECT '102', '258' UNION
SELECT '102', '286' UNION
SELECT '103', '279' UNION
SELECT '103', '285' UNION
SELECT '104', '101' UNION
SELECT '105', '106' UNION
SELECT '106', '105' UNION
SELECT '107', '118' UNION
SELECT '107', '405' UNION
SELECT '107', '558' UNION
SELECT '108', '116' UNION
SELECT '108', '414' UNION
SELECT '109', '349' UNION
SELECT '111', '652' UNION
SELECT '111', '653' UNION
SELECT '112', '139' UNION
SELECT '113', '319' UNION
SELECT '113', '327' UNION
SELECT '114', '326' UNION
SELECT '114', '332' UNION
SELECT '116', '108' UNION
SELECT '117', '270' UNION
SELECT '118', '107' UNION
SELECT '119', '357' UNION
SELECT '120', '142' UNION
SELECT '120', '152' UNION
SELECT '121', '274' UNION
SELECT '122', '123' UNION
SELECT '122', '378' UNION
SELECT '124', '247' UNION
SELECT '126', '125' UNION
SELECT '128', '127' UNION
SELECT '129', '265' UNION
SELECT '129', '277' UNION
SELECT '130', '267' UNION
SELECT '130', '278' UNION
SELECT '131', '372' UNION
SELECT '132', '365' UNION
SELECT '134', '304' UNION
SELECT '135', '143' UNION
SELECT '136', '404' UNION
SELECT '138', '395' UNION
SELECT '139', '112' UNION
SELECT '145', '595' UNION
SELECT '146', '211' UNION
SELECT '147', '591' UNION
SELECT '149', '500' UNION
SELECT '150', '148' UNION
SELECT '151', '155' UNION
SELECT '151', '156' UNION
SELECT '154', '466' UNION
SELECT '157', '514' UNION
SELECT '159', '110' UNION
SELECT '160', '162' UNION
SELECT '162', '160' UNION
SELECT '166', '172' UNION
SELECT '167', '168' UNION
SELECT '169', '424' UNION
SELECT '169', '426' UNION
SELECT '170', '427' UNION
SELECT '170', '428' UNION
SELECT '171', '186' UNION
SELECT '175', '183' UNION
SELECT '179', '173' UNION
SELECT '180', '185' UNION
SELECT '181', '177' UNION
SELECT '182', '174' UNION
SELECT '184', '178' UNION
SELECT '187', '161' UNION
SELECT '188', '165' UNION
SELECT '189', '343' UNION
SELECT '190', '402' UNION
SELECT '195', '192' UNION
SELECT '195', '193' UNION
SELECT '195', '194' UNION
SELECT '197', '309' UNION
SELECT '200', '206' UNION
SELECT '201', '176' UNION
SELECT '201', '198' UNION
SELECT '201', '199' UNION
SELECT '202', '308' UNION
SELECT '205', '429' UNION
SELECT '207', '196' UNION
SELECT '208', '204' UNION
SELECT '209', '509' UNION
SELECT '209', '510' UNION
SELECT '210', '508' UNION
SELECT '211', '146' UNION
SELECT '213', '512' UNION
SELECT '214', '383' UNION
SELECT '215', '212' UNION
SELECT '216', '389' UNION
SELECT '217', '368' UNION
SELECT '217', '369' UNION
SELECT '223', '222' UNION
SELECT '224', '221' UNION
SELECT '225', '144' UNION
SELECT '227', '228' UNION
SELECT '228', '227' UNION
SELECT '229', '230' UNION
SELECT '229', '231' UNION
SELECT '229', '232' UNION
SELECT '233', '218' UNION
SELECT '233', '219' UNION
SELECT '233', '220' UNION
SELECT '234', '493' UNION
SELECT '234', '494' UNION
SELECT '235', '478' UNION
SELECT '236', '158' UNION
SELECT '237', '163' UNION
SELECT '238', '439' UNION
SELECT '238', '440' UNION
SELECT '238', '453' UNION
SELECT '239', '394' UNION
SELECT '240', '164' UNION
SELECT '242', '241' UNION
SELECT '243', '345' UNION
SELECT '244', '245' UNION
SELECT '245', '244' UNION
SELECT '246', '297' UNION
SELECT '246', '354' UNION
SELECT '246', '382' UNION
SELECT '247', '124' UNION
SELECT '249', '489' UNION
SELECT '250', '253' UNION
SELECT '251', '386' UNION
SELECT '251', '387' UNION
SELECT '252', '462' UNION
SELECT '252', '464' UNION
SELECT '252', '550' UNION
SELECT '252', '551' UNION
SELECT '255', '260' UNION
SELECT '256', '248' UNION
SELECT '257', '415' UNION
SELECT '261', '203' UNION
SELECT '262', '264' UNION
SELECT '264', '262' UNION
SELECT '266', '573' UNION
SELECT '268', '306' UNION
SELECT '269', '407' UNION
SELECT '272', '263' UNION
SELECT '272', '273' UNION
SELECT '272', '292' UNION
SELECT '275', '283' UNION
SELECT '276', '401' UNION
SELECT '279', '103' UNION
SELECT '280', '322' UNION
SELECT '281', '457' UNION
SELECT '281', '458' UNION
SELECT '282', '437' UNION
SELECT '282', '438' UNION
SELECT '287', '333' UNION
SELECT '287', '339' UNION
SELECT '287', '409' UNION
SELECT '287', '418' UNION
SELECT '288', '390' UNION
SELECT '289', '290' UNION
SELECT '291', '331' UNION
SELECT '293', '329' UNION
SELECT '298', '463' UNION
SELECT '299', '294' UNION
SELECT '299', '295' UNION
SELECT '299', '296' UNION
SELECT '301', '306' UNION
SELECT '302', '416' UNION
SELECT '303', '410' UNION
SELECT '305', '513' UNION
SELECT '306', '268' UNION
SELECT '306', '300' UNION
SELECT '306', '301' UNION
SELECT '307', '137' UNION
SELECT '310', '345' UNION
SELECT '311', '499' UNION
SELECT '312', '336' UNION
SELECT '313', '582' UNION
SELECT '314', '444' UNION
SELECT '315', '254' UNION
SELECT '316', '459' UNION
SELECT '317', '340' UNION
SELECT '321', '583' UNION
SELECT '323', '318' UNION
SELECT '324', '468' UNION
SELECT '325', '363' UNION
SELECT '337', '338' UNION
SELECT '343', '189' UNION
SELECT '344', '355' UNION
SELECT '346', '342' UNION
SELECT '347', '398' UNION
SELECT '347', '497' UNION
SELECT '347', '569' UNION
SELECT '348', '284' UNION
SELECT '349', '546' UNION
SELECT '349', '547' UNION
SELECT '349', '552' UNION
SELECT '349', '553' UNION
SELECT '350', '371' UNION
SELECT '357', '542' UNION
SELECT '357', '543' UNION
SELECT '357', '544' UNION
SELECT '360', '330' UNION
SELECT '360', '341' UNION
SELECT '360', '359' UNION
SELECT '361', '334' UNION
SELECT '361', '335' UNION
SELECT '361', '358' UNION
SELECT '362', '351' UNION
SELECT '362', '352' UNION
SELECT '362', '356' UNION
SELECT '364', '320' UNION
SELECT '364', '328' UNION
SELECT '364', '353' UNION
SELECT '366', '133' UNION
SELECT '370', '473' UNION
SELECT '375', '367' UNION
SELECT '375', '373' UNION
SELECT '376', '534' UNION
SELECT '376', '535' UNION
SELECT '376', '536' UNION
SELECT '376', '537' UNION
SELECT '377', '374' UNION
SELECT '377', '565' UNION
SELECT '379', '534' UNION
SELECT '379', '535' UNION
SELECT '379', '536' UNION
SELECT '379', '537' UNION
SELECT '384', '502' UNION
SELECT '388', '271' UNION
SELECT '392', '397' UNION
SELECT '393', '400' UNION
SELECT '396', '381' UNION
SELECT '399', '380' UNION
SELECT '403', '385' UNION
SELECT '405', '107' UNION
SELECT '406', '226' UNION
SELECT '408', '576' UNION
SELECT '411', '121' UNION
SELECT '411', '274' UNION
SELECT '412', '413' UNION
SELECT '414', '108' UNION
SELECT '417', '577' UNION
SELECT '419', '117' UNION
SELECT '420', '391' UNION
SELECT '421', '191' UNION
SELECT '422', '452' UNION
SELECT '425', '460' UNION
SELECT '430', '432' UNION
SELECT '433', '482' UNION
SELECT '434', '433' UNION
SELECT '434', '481' UNION
SELECT '434', '482' UNION
SELECT '435', '259' UNION
SELECT '436', '568' UNION
SELECT '439', '238' UNION
SELECT '442', '443' UNION
SELECT '443', '442' UNION
SELECT '445', '446' UNION
SELECT '447', '448' UNION
SELECT '449', '567' UNION
SELECT '450', '451' UNION
SELECT '451', '450' UNION
SELECT '454', '455' UNION
SELECT '456', '566' UNION
SELECT '461', '441' UNION
SELECT '465', '100' UNION
SELECT '466', '154' UNION
SELECT '467', '533' UNION
SELECT '469', '474' UNION
SELECT '470', '471' UNION
SELECT '470', '475' UNION
SELECT '470', '476' UNION
SELECT '471', '470' UNION
SELECT '471', '475' UNION
SELECT '471', '476' UNION
SELECT '472', '496' UNION
SELECT '474', '469' UNION
SELECT '475', '470' UNION
SELECT '475', '471' UNION
SELECT '475', '476' UNION
SELECT '476', '470' UNION
SELECT '476', '471' UNION
SELECT '476', '475' UNION
SELECT '477', '498' UNION
SELECT '479', '480' UNION
SELECT '480', '479' UNION
SELECT '481', '482' UNION
SELECT '482', '433' UNION
SELECT '482', '434' UNION
SELECT '482', '481' UNION
SELECT '483', '484' UNION
SELECT '485', '431' UNION
SELECT '485', '486' UNION
SELECT '485', '503' UNION
SELECT '485', '504' UNION
SELECT '486', '485' UNION
SELECT '487', '488' UNION
SELECT '488', '487' UNION
SELECT '491', '490' UNION
SELECT '495', '140' UNION
SELECT '496', '472' UNION
SELECT '498', '477' UNION
SELECT '501', '141' UNION
SELECT '503', '504' UNION
SELECT '504', '503' UNION
SELECT '505', '431' UNION
SELECT '505', '485' UNION
SELECT '505', '486' UNION
SELECT '505', '503' UNION
SELECT '505', '504' UNION
SELECT '505', '506' UNION
SELECT '506', '505' UNION
SELECT '507', '115' UNION
SELECT '511', '492' UNION
SELECT '515', '516' UNION
SELECT '517', '518' UNION
SELECT '518', '517' UNION
SELECT '519', '520' UNION
SELECT '519', '521' UNION
SELECT '519', '522' UNION
SELECT '519', '523' UNION
SELECT '519', '524' UNION
SELECT '529', '525' UNION
SELECT '529', '526' UNION
SELECT '529', '527' UNION
SELECT '529', '528' UNION
SELECT '529', '530' UNION
SELECT '531', '570' UNION
SELECT '531', '571' UNION
SELECT '531', '572' UNION
SELECT '533', '532' UNION
SELECT '540', '575' UNION
SELECT '541', '538' UNION
SELECT '545', '556' UNION
SELECT '545', '557' UNION
SELECT '548', '539' UNION
SELECT '548', '549' UNION
SELECT '554', '555' UNION
SELECT '559', '560' UNION
SELECT '559', '561' UNION
SELECT '559', '562' UNION
SELECT '559', '563' UNION
SELECT '573', '574' UNION
SELECT '578', '579' UNION
SELECT '578', '664' UNION
SELECT '579', '578' UNION
SELECT '579', '663' UNION
SELECT '580', '564' UNION
SELECT '581', '423' UNION
SELECT '582', '313' UNION
SELECT '583', '321' UNION
SELECT '585', '587' UNION
SELECT '585', '593' UNION
SELECT '586', '588' UNION
SELECT '587', '585' UNION
SELECT '588', '586' UNION
SELECT '588', '596' UNION
SELECT '589', '590' UNION
SELECT '591', '147' UNION
SELECT '592', '651' UNION
SELECT '592', '666' UNION
SELECT '593', '585' UNION
SELECT '594', '584' UNION
SELECT '596', '588' UNION
SELECT '597', '661' UNION
SELECT '599', '602' UNION
SELECT '603', '600' UNION
SELECT '604', '601' UNION
SELECT '605', '608' UNION
SELECT '605', '611' UNION
SELECT '606', '669' UNION
SELECT '608', '605' UNION
SELECT '608', '611' UNION
SELECT '609', '607' UNION
SELECT '610', '613' UNION
SELECT '611', '605' UNION
SELECT '611', '608' UNION
SELECT '612', '630' UNION
SELECT '613', '610' UNION
SELECT '614', '615' UNION
SELECT '615', '614' UNION
SELECT '617', '616' UNION
SELECT '618', '620' UNION
SELECT '619', '623' UNION
SELECT '621', '622' UNION
SELECT '624', '655' UNION
SELECT '624', '656' UNION
SELECT '624', '657' UNION
SELECT '627', '626' UNION
SELECT '628', '626' UNION
SELECT '629', '626' UNION
SELECT '630', '612' UNION
SELECT '631', '626' UNION
SELECT '632', '633' UNION
SELECT '633', '632' UNION
SELECT '634', '635' UNION
SELECT '635', '634' UNION
SELECT '637', '636' UNION
SELECT '638', '639' UNION
SELECT '640', '641' UNION
SELECT '641', '640' UNION
SELECT '642', '598' UNION
SELECT '643', '625' UNION
SELECT '644', '645' UNION
SELECT '645', '644' UNION
SELECT '646', '665' UNION
SELECT '647', '660' UNION
SELECT '648', '654' UNION
SELECT '649', '655' UNION
SELECT '649', '656' UNION
SELECT '649', '657' UNION
SELECT '650', '659' UNION
SELECT '661', '597' UNION
SELECT '662', '658' UNION
SELECT '668', '667' UNION
SELECT '670', '153' UNION
SELECT '671', '672'
CREATE TABLE #NewRelations
(
ColID INT,
ColName VARCHAR(32)
)
DECLARE @MainID VARCHAR(32)
DECLARE @ColD INT
DECLARE @RelIDs TABLE
(
IDName VARCHAR(32)
)
SET @ColD = 1
DECLARE cur_IDName CURSOR FOR
SELECT Main FROM #Relations
UNION
SELECT Related FROM #Relations
ORDER BY 1
OPEN cur_IDName
FETCH NEXT FROM cur_IDName INTO @MainID
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE @RelIDs
INSERT @RelIDs SELECT @MainID
WHILE @@ROWCOUNT > 0
INSERT @RelIDs
SELECT C.IDName FROM
(
SELECT Related IDName
FROM
#Relations A
JOIN
@RelIDs B
ON
A.Main = B.IDName) C
LEFT OUTER JOIN
@RelIDs D
ON
C.IDName = D.IDName
WHERE
D.IDName IS NULL
UNION
SELECT C.IDName FROM
(
SELECT Main IDName
FROM
#Relations A
JOIN
@RelIDs B
ON
A.Related = B.IDName) C
LEFT OUTER JOIN
@RelIDs D
ON
C.IDName = D.IDName
WHERE
D.IDName IS NULL
INSERT #NewRelations (ColID, ColName)
SELECT DISTINCT @ColD, IDName
FROM
@RelIDs A
LEFT OUTER JOIN
#NewRelations B
ON
A.IDName = B.ColName
WHERE
B.ColName IS NULL
IF @@ROWCOUNT > 0
SET @ColD = @ColD + 1
FETCH NEXT FROM cur_IDName INTO @MainID
END
CLOSE cur_IDName
DEALLOCATE cur_IDName
SELECT * FROM #NewRelations
Regards,
gova
July 25, 2006 at 8:06 am
The result is not formatted as yours but you can see the related sets. I checked the execution times and there is a big improvement.
The base ideea is to transform the graph into a tree with the smallest value into a relation as root to the other one.
---------------------------------------------------
SET NOCOUNT ON
DECLARE @Relations TABLE
(Main VARCHAR(10),Related VARCHAR(10))
INSERT @Relations
SELECT '100', '465' UNION
SELECT '101', '104' UNION
SELECT '102', '258' UNION
SELECT '102', '286' UNION
SELECT '103', '279' UNION
SELECT '103', '285' UNION
SELECT '104', '101' UNION
SELECT '105', '106' UNION
SELECT '106', '105' UNION
SELECT '107', '118' UNION
SELECT '107', '405' UNION
SELECT '107', '558' UNION
SELECT '108', '116' UNION
SELECT '108', '414' UNION
SELECT '109', '349' UNION
SELECT '111', '652' UNION
SELECT '111', '653' UNION
SELECT '112', '139' UNION
SELECT '113', '319' UNION
SELECT '113', '327' UNION
SELECT '114', '326' UNION
SELECT '114', '332' UNION
SELECT '116', '108' UNION
SELECT '117', '270' UNION
SELECT '118', '107' UNION
SELECT '119', '357' UNION
SELECT '120', '142' UNION
SELECT '120', '152' UNION
SELECT '121', '274' UNION
SELECT '122', '123' UNION
SELECT '122', '378' UNION
SELECT '124', '247' UNION
SELECT '126', '125' UNION
SELECT '128', '127' UNION
SELECT '129', '265' UNION
SELECT '129', '277' UNION
SELECT '130', '267' UNION
SELECT '130', '278' UNION
SELECT '131', '372' UNION
SELECT '132', '365' UNION
SELECT '134', '304' UNION
SELECT '135', '143' UNION
SELECT '136', '404' UNION
SELECT '138', '395' UNION
SELECT '139', '112' UNION
SELECT '145', '595' UNION
SELECT '146', '211' UNION
SELECT '147', '591' UNION
SELECT '149', '500' UNION
SELECT '150', '148' UNION
SELECT '151', '155' UNION
SELECT '151', '156' UNION
SELECT '154', '466' UNION
SELECT '157', '514' UNION
SELECT '159', '110' UNION
SELECT '160', '162' UNION
SELECT '162', '160' UNION
SELECT '166', '172' UNION
SELECT '167', '168' UNION
SELECT '169', '424' UNION
SELECT '169', '426' UNION
SELECT '170', '427' UNION
SELECT '170', '428' UNION
SELECT '171', '186' UNION
SELECT '175', '183' UNION
SELECT '179', '173' UNION
SELECT '180', '185' UNION
SELECT '181', '177' UNION
SELECT '182', '174' UNION
SELECT '184', '178' UNION
SELECT '187', '161' UNION
SELECT '188', '165' UNION
SELECT '189', '343' UNION
SELECT '190', '402' UNION
SELECT '195', '192' UNION
SELECT '195', '193' UNION
SELECT '195', '194' UNION
SELECT '197', '309' UNION
SELECT '200', '206' UNION
SELECT '201', '176' UNION
SELECT '201', '198' UNION
SELECT '201', '199' UNION
SELECT '202', '308' UNION
SELECT '205', '429' UNION
SELECT '207', '196' UNION
SELECT '208', '204' UNION
SELECT '209', '509' UNION
SELECT '209', '510' UNION
SELECT '210', '508' UNION
SELECT '211', '146' UNION
SELECT '213', '512' UNION
SELECT '214', '383' UNION
SELECT '215', '212' UNION
SELECT '216', '389' UNION
SELECT '217', '368' UNION
SELECT '217', '369' UNION
SELECT '223', '222' UNION
SELECT '224', '221' UNION
SELECT '225', '144' UNION
SELECT '227', '228' UNION
SELECT '228', '227' UNION
SELECT '229', '230' UNION
SELECT '229', '231' UNION
SELECT '229', '232' UNION
SELECT '233', '218' UNION
SELECT '233', '219' UNION
SELECT '233', '220' UNION
SELECT '234', '493' UNION
SELECT '234', '494' UNION
SELECT '235', '478' UNION
SELECT '236', '158' UNION
SELECT '237', '163' UNION
SELECT '238', '439' UNION
SELECT '238', '440' UNION
SELECT '238', '453' UNION
SELECT '239', '394' UNION
SELECT '240', '164' UNION
SELECT '242', '241' UNION
SELECT '243', '345' UNION
SELECT '244', '245' UNION
SELECT '245', '244' UNION
SELECT '246', '297' UNION
SELECT '246', '354' UNION
SELECT '246', '382' UNION
SELECT '247', '124' UNION
SELECT '249', '489' UNION
SELECT '250', '253' UNION
SELECT '251', '386' UNION
SELECT '251', '387' UNION
SELECT '252', '462' UNION
SELECT '252', '464' UNION
SELECT '252', '550' UNION
SELECT '252', '551' UNION
SELECT '255', '260' UNION
SELECT '256', '248' UNION
SELECT '257', '415' UNION
SELECT '261', '203' UNION
SELECT '262', '264' UNION
SELECT '264', '262' UNION
SELECT '266', '573' UNION
SELECT '268', '306' UNION
SELECT '269', '407' UNION
SELECT '272', '263' UNION
SELECT '272', '273' UNION
SELECT '272', '292' UNION
SELECT '275', '283' UNION
SELECT '276', '401' UNION
SELECT '279', '103' UNION
SELECT '280', '322' UNION
SELECT '281', '457' UNION
SELECT '281', '458' UNION
SELECT '282', '437' UNION
SELECT '282', '438' UNION
SELECT '287', '333' UNION
SELECT '287', '339' UNION
SELECT '287', '409' UNION
SELECT '287', '418' UNION
SELECT '288', '390' UNION
SELECT '289', '290' UNION
SELECT '291', '331' UNION
SELECT '293', '329' UNION
SELECT '298', '463' UNION
SELECT '299', '294' UNION
SELECT '299', '295' UNION
SELECT '299', '296' UNION
SELECT '301', '306' UNION
SELECT '302', '416' UNION
SELECT '303', '410' UNION
SELECT '305', '513' UNION
SELECT '306', '268' UNION
SELECT '306', '300' UNION
SELECT '306', '301' UNION
SELECT '307', '137' UNION
SELECT '310', '345' UNION
SELECT '311', '499' UNION
SELECT '312', '336' UNION
SELECT '313', '582' UNION
SELECT '314', '444' UNION
SELECT '315', '254' UNION
SELECT '316', '459' UNION
SELECT '317', '340' UNION
SELECT '321', '583' UNION
SELECT '323', '318' UNION
SELECT '324', '468' UNION
SELECT '325', '363' UNION
SELECT '337', '338' UNION
SELECT '343', '189' UNION
SELECT '344', '355' UNION
SELECT '346', '342' UNION
SELECT '347', '398' UNION
SELECT '347', '497' UNION
SELECT '347', '569' UNION
SELECT '348', '284' UNION
SELECT '349', '546' UNION
SELECT '349', '547' UNION
SELECT '349', '552' UNION
SELECT '349', '553' UNION
SELECT '350', '371' UNION
SELECT '357', '542' UNION
SELECT '357', '543' UNION
SELECT '357', '544' UNION
SELECT '360', '330' UNION
SELECT '360', '341' UNION
SELECT '360', '359' UNION
SELECT '361', '334' UNION
SELECT '361', '335' UNION
SELECT '361', '358' UNION
SELECT '362', '351' UNION
SELECT '362', '352' UNION
SELECT '362', '356' UNION
SELECT '364', '320' UNION
SELECT '364', '328' UNION
SELECT '364', '353' UNION
SELECT '366', '133' UNION
SELECT '370', '473' UNION
SELECT '375', '367' UNION
SELECT '375', '373' UNION
SELECT '376', '534' UNION
SELECT '376', '535' UNION
SELECT '376', '536' UNION
SELECT '376', '537' UNION
SELECT '377', '374' UNION
SELECT '377', '565' UNION
SELECT '379', '534' UNION
SELECT '379', '535' UNION
SELECT '379', '536' UNION
SELECT '379', '537' UNION
SELECT '384', '502' UNION
SELECT '388', '271' UNION
SELECT '392', '397' UNION
SELECT '393', '400' UNION
SELECT '396', '381' UNION
SELECT '399', '380' UNION
SELECT '403', '385' UNION
SELECT '405', '107' UNION
SELECT '406', '226' UNION
SELECT '408', '576' UNION
SELECT '411', '121' UNION
SELECT '411', '274' UNION
SELECT '412', '413' UNION
SELECT '414', '108' UNION
SELECT '417', '577' UNION
SELECT '419', '117' UNION
SELECT '420', '391' UNION
SELECT '421', '191' UNION
SELECT '422', '452' UNION
SELECT '425', '460' UNION
SELECT '430', '432' UNION
SELECT '433', '482' UNION
SELECT '434', '433' UNION
SELECT '434', '481' UNION
SELECT '434', '482' UNION
SELECT '435', '259' UNION
SELECT '436', '568' UNION
SELECT '439', '238' UNION
SELECT '442', '443' UNION
SELECT '443', '442' UNION
SELECT '445', '446' UNION
SELECT '447', '448' UNION
SELECT '449', '567' UNION
SELECT '450', '451' UNION
SELECT '451', '450' UNION
SELECT '454', '455' UNION
SELECT '456', '566' UNION
SELECT '461', '441' UNION
SELECT '465', '100' UNION
SELECT '466', '154' UNION
SELECT '467', '533' UNION
SELECT '469', '474' UNION
SELECT '470', '471' UNION
SELECT '470', '475' UNION
SELECT '470', '476' UNION
SELECT '471', '470' UNION
SELECT '471', '475' UNION
SELECT '471', '476' UNION
SELECT '472', '496' UNION
SELECT '474', '469' UNION
SELECT '475', '470' UNION
SELECT '475', '471' UNION
SELECT '475', '476' UNION
SELECT '476', '470' UNION
SELECT '476', '471' UNION
SELECT '476', '475' UNION
SELECT '477', '498' UNION
SELECT '479', '480' UNION
SELECT '480', '479' UNION
SELECT '481', '482' UNION
SELECT '482', '433' UNION
SELECT '482', '434' UNION
SELECT '482', '481' UNION
SELECT '483', '484' UNION
SELECT '485', '431' UNION
SELECT '485', '486' UNION
SELECT '485', '503' UNION
SELECT '485', '504' UNION
SELECT '486', '485' UNION
SELECT '487', '488' UNION
SELECT '488', '487' UNION
SELECT '491', '490' UNION
SELECT '495', '140' UNION
SELECT '496', '472' UNION
SELECT '498', '477' UNION
SELECT '501', '141' UNION
SELECT '503', '504' UNION
SELECT '504', '503' UNION
SELECT '505', '431' UNION
SELECT '505', '485' UNION
SELECT '505', '486' UNION
SELECT '505', '503' UNION
SELECT '505', '504' UNION
SELECT '505', '506' UNION
SELECT '506', '505' UNION
SELECT '507', '115' UNION
SELECT '511', '492' UNION
SELECT '515', '516' UNION
SELECT '517', '518' UNION
SELECT '518', '517' UNION
SELECT '519', '520' UNION
SELECT '519', '521' UNION
SELECT '519', '522' UNION
SELECT '519', '523' UNION
SELECT '519', '524' UNION
SELECT '529', '525' UNION
SELECT '529', '526' UNION
SELECT '529', '527' UNION
SELECT '529', '528' UNION
SELECT '529', '530' UNION
SELECT '531', '570' UNION
SELECT '531', '571' UNION
SELECT '531', '572' UNION
SELECT '533', '532' UNION
SELECT '540', '575' UNION
SELECT '541', '538' UNION
SELECT '545', '556' UNION
SELECT '545', '557' UNION
SELECT '548', '539' UNION
SELECT '548', '549' UNION
SELECT '554', '555' UNION
SELECT '559', '560' UNION
SELECT '559', '561' UNION
SELECT '559', '562' UNION
SELECT '559', '563' UNION
SELECT '573', '574' UNION
SELECT '578', '579' UNION
SELECT '578', '664' UNION
SELECT '579', '578' UNION
SELECT '579', '663' UNION
SELECT '580', '564' UNION
SELECT '581', '423' UNION
SELECT '582', '313' UNION
SELECT '583', '321' UNION
SELECT '585', '587' UNION
SELECT '585', '593' UNION
SELECT '586', '588' UNION
SELECT '587', '585' UNION
SELECT '588', '586' UNION
SELECT '588', '596' UNION
SELECT '589', '590' UNION
SELECT '591', '147' UNION
SELECT '592', '651' UNION
SELECT '592', '666' UNION
SELECT '593', '585' UNION
SELECT '594', '584' UNION
SELECT '596', '588' UNION
SELECT '597', '661' UNION
SELECT '599', '602' UNION
SELECT '603', '600' UNION
SELECT '604', '601' UNION
SELECT '605', '608' UNION
SELECT '605', '611' UNION
SELECT '606', '669' UNION
SELECT '608', '605' UNION
SELECT '608', '611' UNION
SELECT '609', '607' UNION
SELECT '610', '613' UNION
SELECT '611', '605' UNION
SELECT '611', '608' UNION
SELECT '612', '630' UNION
SELECT '613', '610' UNION
SELECT '614', '615' UNION
SELECT '615', '614' UNION
SELECT '617', '616' UNION
SELECT '618', '620' UNION
SELECT '619', '623' UNION
SELECT '621', '622' UNION
SELECT '624', '655' UNION
SELECT '624', '656' UNION
SELECT '624', '657' UNION
SELECT '627', '626' UNION
SELECT '628', '626' UNION
SELECT '629', '626' UNION
SELECT '630', '612' UNION
SELECT '631', '626' UNION
SELECT '632', '633' UNION
SELECT '633', '632' UNION
SELECT '634', '635' UNION
SELECT '635', '634' UNION
SELECT '637', '636' UNION
SELECT '638', '639' UNION
SELECT '640', '641' UNION
SELECT '641', '640' UNION
SELECT '642', '598' UNION
SELECT '643', '625' UNION
SELECT '644', '645' UNION
SELECT '645', '644' UNION
SELECT '646', '665' UNION
SELECT '647', '660' UNION
SELECT '648', '654' UNION
SELECT '649', '655' UNION
SELECT '649', '656' UNION
SELECT '649', '657' UNION
SELECT '650', '659' UNION
SELECT '661', '597' UNION
SELECT '662', '658' UNION
SELECT '668', '667' UNION
SELECT '670', '153' UNION
SELECT '671', '672'
--SELECT * FROM @Relations
DECLARE @rows INT
SET @rows=1
WHILE @rows>0
BEGIN
UPDATE A
SET Main=Related,Related=Main
FROM @Relations A
WHERE Main>Related
UPDATE A
SET A.Main=B.Main
FROM @Relations A INNER JOIN @Relations B ON
A.Main=B.Related
SET @Rows=@@rowcount
UPDATE A
SET A.Related=B.MaxMain
FROM @Relations A INNER JOIN (SELECT MIN(MAIN) as MinMain, MAX(Main) as MaxMain,Related FROM @Relations GROUP BY related HAVING COUNT(*) >=2) B
ON A.Main=B.MinMain AND A.Related=B.Related
WHERE MinMain<>MaxMain
SET @Rows=@Rows+@@rowcount
END
SELECT * FROM @Relations
--add the ROOT nodes to relative column
/*
UNION ALL
SELECT a.Main,a.Main FROM
@Relations A LEFT OUTER JOIN @Relations B
ON A.Main=B.Related
WHERE B.Related IS NULL
GROUP BY a.Main
ORDER by MAIN
*/
---------------------------------------------------
Vasc
July 25, 2006 at 2:19 pm
Great ! Works faster.
Thank You very much.
Regards,
gova
July 26, 2006 at 3:23 am
And just a quick note to get the RelationGroup...
Put
select (select count(distinct main) from @relations r where r.main <= z.main) relationgroup, related
from (
and
) z
order by related
around the last select in Vasc solution like this
select (select count(distinct main) from @relations r where r.main <= z.main) relationgroup, related
from (
SELECT Main, Related FROM @Relations
UNION ALL
SELECT a.Main,a.Main FROM
@Relations A LEFT OUTER JOIN @Relations B
ON A.Main=B.Related
WHERE B.Related IS NULL
GROUP BY a.Main
) z
order by related
And you get following output for original test data
1 AA
1 BB
1 CC
1 DD
1 EE
2 MM
2 NN
2 SS
3 WW
3 XX
3 YY
N 56°04'39.16"
E 12°55'05.25"
July 26, 2006 at 7:05 am
Thanks Peter.
With the help of this http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
I made the same solution.
Regards,
gova
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply