September 24, 2010 at 8:32 am
This similar to my previous post. I did not do my home work properly then. My requirement is little more complicated.
DECLARE @MyRelations TABLE
(
SubjectID VARCHAR(32),
RelatedID VARCHAR(32)
)
INSERT @MyRelations
SELECT 'ADF1', 'BFSA9' UNION
SELECT 'ADF1', 'CAAAAA@AS' UNION
SELECT 'CAAAAA@AS', 'DAS5' UNION
SELECT 'BFSA9', 'EDS6' UNION
SELECT 'XSD4', 'YGASLA@SD' UNION
SELECT 'XSD4', 'ZDF8'UNION
SELECT 'MAS61', 'DSD25' UNION
SELECT 'DSD25', 'DFG6' UNION
SELECT 'DSD25', 'ERT2'
DECLARE @mymaster TABLE
(
AllIDVARCHAR(32),
IsParentBIT
)
INSERT @mymaster
SELECT 'ADF1', 0 UNION
SELECT 'BFSA9', 0 UNION
SELECT 'CAAAAA@AS', 1 UNION
SELECT 'DAS5', 0 UNION
SELECT 'EDS6', 0 UNION
SELECT 'XSD4', 0 UNION
SELECT 'YGASLA@SD', 1 UNION
SELECT 'ZDF8', 0 UNION
SELECT 'DFG6', 0 UNION
SELECT 'DSD25', 0 UNION
SELECT 'ERT2', 0 UNION
SELECT 'MAS61', 0
DECLARE
ADF1 is Related to BFSA9
ADF1 is Related to CAAAAA@AS
CAAAAA@AS is Related to DAS5
BFSA9 is Related to EDS6
So ADF1, BFSA9, CAAAAA@AS, DAS5, EDS6 are all a family
Leader or parent is defined in the table @mymaster
When there is no parent still they are related like resultset groupid 3
And I neeed to assign each related group a separate number
The result set this case would be
GroupID Parent Relations
-------------------------------------------
1CAAAAA@ASADF1
1CAAAAA@ASBFSA9
1CAAAAA@ASDAS5
1CAAAAA@ASEDS6
2YGASLA@SDXSD4
2YGASLA@SDZDF8
3NULLMAS61
3NULLDSD25
3NULLDFG6
3NULLERT2
September 25, 2010 at 12:05 pm
Is there ever a possibility of circular references?
For example
INSERT @MyRelations
SELECT 'ADF1', 'BFSA9' UNION
SELECT 'BFSA9', 'CAAAAA@AS' UNION
SELECT 'CAAAAA@AS','ADF1'
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply