February 28, 2011 at 12:43 pm
Hi
I have a table
RelationshipTable
(
RelationshipTableID int,
MasterID int,
RelatedID int
Relationship varchar(30)
)
Both MasterID and RelatedID are FK from the same Table MasterRecords. The design of the table cannot be changed. The data looks like this
MasterIDRelatedIDRelationship
200201Parent
201200Child
201202Parent
202201Child
202203Parent
203202Child
203204Parent
204203Child
204205Parent
205204Child
205206Parent
206205Child
206207Parent
207206Child
207208Parent
208207Child
208209Parent
209208Child
209210Parent
210209Child
210211Parent
211210Child
211212Parent
212211Child
Given MasterID = 200. I need to find all the related records. I have a CTE which looks like this.
WITH
related_CTE
(RelationshipTableID,
MASTERID,
RELATEDID,
RELATIONSHIP) AS
(
SELECT RelationshipTableID, MasterID, RelatedID, Relationship
FROM RelationshipTable
WHERE (RelationshipTable.MasterID = 200) AND (RelationshipTable.relationship IN ('Parent', 'Child'))
union all
select RelationshipTable.RelationshipTableID,RelationshipTable.MasterID,RelationshipTable.relatedID,RelationshipTable.relationship
from RelationshipTable
inner join related_CTE on RelationshipTable.RelatedID = related_CTE.masterid
and RelationshipTable.relationship in ('Parent','Child')
)
select * from related_CTE OPTION (MAXRECURSION 200);
I get an error on the recursion limit.
Any help on this query is greatly appreciated
February 28, 2011 at 1:21 pm
Here is the code for the sample data
create table RelationshipTable
(
RelationshipTableID int not null identity,
MasterID int null,
RelatedID int null,
Relationship varchar(30) null
)
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (200,201,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (201,200,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (201,202,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (202,201,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (202,203,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (203,202,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (203,204,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (204,203,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (204,205,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (205,204,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (205,206,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (206,205,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (206,207,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (207,206,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (207,208,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (208,207,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (208,209,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (209,208,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (209,210,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (210,209,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (210,211,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (211,210,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (211,212,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (212,211,'Child')
February 28, 2011 at 1:53 pm
The issue is caused by your table design:
Since the same information is inserted twice you're forcing a referential loop.
Example:
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (200,201,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (201,200,'Child')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (201,202,'Parent')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (202,201,'Child')
There are two entries meaning the same: 200-> 201 'Parent' is IMHO equivalent to 201,200,'Child' respectively 201,202,'Parent' and 202,201,'Child'
You should rethink your data model to get rid of the redundancies. Especially if this design is open for data inconsistency:
What is the correct result of 201,202,'Parent' and 203,201,'Child' being the only two rows?
In the meantime, here's an approach that doesn't throw an error. I'm not sure if the results returned are correct though...
WITH
related_CTE
(RelationshipTableID,
MASTERID,
RELATEDID,
RELATIONSHIP) AS
(
SELECT RelationshipTableID, MasterID, RelatedID, Relationship
FROM RelationshipTable
WHERE (RelationshipTable.MasterID = 200) AND (RelationshipTable.relationship IN ('Parent'))
union all
select RelationshipTable.RelationshipTableID,RelationshipTable.MasterID,RelationshipTable.relatedID,RelationshipTable.relationship
from RelationshipTable
inner join related_CTE on RelationshipTable.RelatedID = related_CTE.masterid
and RelationshipTable.relationship in ('Child')
)
select * from related_CTE;
February 28, 2011 at 2:03 pm
Hi Lutz
Thanks for your reply.
Changing the table design is out of my hands and would require an act of congress.
I have tried your solution. This wont work if you use MasterID 205. MasterID 205 is in turn the child of 204 and so on. This is almost like me bringing out the complete family of a particular MasterID. All the possible parents and children.
February 28, 2011 at 2:16 pm
Sorry for missing the requirement to search the family tree up and down for various members somewhere in the middle of the structure. 😉
There might be easier solutions available but I would use two cte's, one finding all childs and the other one finding all parents and merge the two result sets.
WITH
related_CTE
(RelationshipTableID,
MASTERID,
RELATEDID,
RELATIONSHIP) AS
(
SELECT RelationshipTableID, MasterID, RelatedID, Relationship
FROM RelationshipTable
WHERE (RelationshipTable.MasterID = 205) AND (RelationshipTable.relationship IN ('Parent'))
UNION ALL
SELECT RelationshipTable.RelationshipTableID,RelationshipTable.MasterID,RelationshipTable.relatedID,RelationshipTable.relationship
FROM RelationshipTable
INNER JOIN related_CTE ON RelationshipTable.RelatedID = related_CTE.masterid
AND RelationshipTable.relationship IN ('Child')
),
related_parent_CTE
(RelationshipTableID,
MASTERID,
RELATEDID,
RELATIONSHIP) AS
(
SELECT RelationshipTableID, MasterID, RelatedID, Relationship
FROM RelationshipTable
WHERE (RelationshipTable.MasterID = 205) AND (RelationshipTable.relationship IN ('Parent'))
UNION ALL
SELECT RelationshipTable.RelationshipTableID,RelationshipTable.MasterID,RelationshipTable.relatedID,RelationshipTable.relationship
FROM RelationshipTable
INNER JOIN related_parent_CTE ON RelationshipTable.RelatedID = related_parent_CTE.masterid
AND RelationshipTable.relationship IN ('Parent')
)
SELECT * FROM related_CTE
UNION ALL
SELECT * FROM related_parent_CTE
;
February 28, 2011 at 2:35 pm
Hi Lutz
Thank you for that response. The code worked. Here is my problem. My relationship comes from another table where the relationship is defined as a fromdirection and a todirection.
RelationShipName FromDirection ToDirection
Parent-Child Parent Child
Weird relationship Weird Weird
In one of the relationships the values in the relationship column of the table is same.
As in
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (500,501,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (501,502,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (502,503,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (503,504,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (504,505,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (505,506,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (506,507,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (507,508,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (508,509,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (509,510,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (510,511,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (511,512,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (512,513,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (501,500,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (502,501,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (503,502,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (504,503,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (505,504,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (506,505,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (507,506,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (508,507,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (509,508,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (510,509,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (511,510,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (512,511,'Weird')
insert into RelationshipTable (MasterID,RelatedID,Relationship) values (513,512,'Weird')
Any ideas
February 28, 2011 at 2:53 pm
I added a check for MASTERID>RELATEDID rsp. MASTERID<RELATEDID.
Since this returned duplicate values, I also had to change UNION ALL to UNION.
WITH
related_CTE
(RelationshipTableID,
MASTERID,
RELATEDID,
RELATIONSHIP) AS
(
SELECT RelationshipTableID, MasterID, RelatedID, Relationship
FROM RelationshipTable
WHERE (RelationshipTable.MasterID = 502) AND (RelationshipTable.relationship IN ('Weird'))
UNION ALL
SELECT RelationshipTable.RelationshipTableID,RelationshipTable.MasterID,RelationshipTable.relatedID,RelationshipTable.relationship
FROM RelationshipTable
INNER JOIN related_CTE ON RelationshipTable.RelatedID = related_CTE.masterid
AND RelationshipTable.relationship NOT IN ('Weird')
AND RelationshipTable.MASTERID>RelationshipTable.RELATEDID
),
related_parent_CTE
(RelationshipTableID,
MASTERID,
RELATEDID,
RELATIONSHIP) AS
(
SELECT RelationshipTableID, MasterID, RelatedID, Relationship
FROM RelationshipTable
WHERE (RelationshipTable.MasterID = 502) AND (RelationshipTable.relationship IN ('Weird'))
UNION ALL
SELECT RelationshipTable.RelationshipTableID,RelationshipTable.MasterID,RelationshipTable.relatedID,RelationshipTable.relationship
FROM RelationshipTable
INNER JOIN related_parent_CTE ON RelationshipTable.RelatedID = related_parent_CTE.masterid
AND RelationshipTable.relationship IN ('Weird')
AND RelationshipTable.MASTERID<RelationshipTable.RELATEDID
)
SELECT * FROM related_CTE
UNION
SELECT * FROM related_parent_CTE
;
March 1, 2011 at 7:47 am
Hi Lutz
The solution worked brilliantly. Many thanks.
Could you please explain the RelationshipTable.MASTERID>RelationshipTable.RELATEDID and the RelationshipTable.MASTERID<RelationshipTable.RELATEDID
once again, many thanks
March 1, 2011 at 12:09 pm
badamaams (3/1/2011)
Hi LutzThe solution worked brilliantly. Many thanks.
Could you please explain the RelationshipTable.MASTERID>RelationshipTable.RELATEDID and the RelationshipTable.MASTERID<RelationshipTable.RELATEDID
once again, many thanks
Glad I could help 😀
I used the MASTERID/RELATEDID comparison as shown above in order to avoid infinite loops that would cause the recursion limit your mentioned originally.
In a scenario with 200,201 and 201,200 without any additional information to differentiate (like Parent'/'child') you'd end up with an infinite loop since both would reference each other.
Using the comparison as shown above can be used to select all child elements (Master>Related) and parents (Master<Related) without the infinite self reference.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply