StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POrecursive query with peer relations
text
Body
copied!<p>Let's say there is a table of relationships (entity_id, relationship, related_id)</p> <pre><code>1, A, 2 1, A, 3 3, B, 5 1, C, null 12, C, 1 100, C, null </code></pre> <p>I need a query that will pull all related rows. For example, if i queried for entity_id = 1, the following rows should be pulled</p> <pre><code>1, A, 2 1, A, 3 3, B, 5 1, C, null 12, C, 1 </code></pre> <p>Actually, if i queried for entity_id = 1, 2, 3, 5, or 12, the resultset should be the same.</p> <p>This is different than the standard manager-employee paradigm as there is no hierarchy. The relationships can go in any direction. </p> <hr> <p><strong>EDIT</strong> None of the answers posted thus far worked.</p> <p>I was able to come up with a solution that works.</p> <p>I'll give the solution credit to the one who can clean this monstrosity into something more elegant.</p> <pre><code>with tab as ( -- union for reversals select id, entity_id, r.related_id, 1 level , cast('/' + cast(entity_id as varchar(1000)) + '/' as varchar(1000)) path from _entity_relation r where not exists(select null from _entity_relation r2 where r2.related_id=r.entity_id) or r.related_id is null union select id, related_id, r.entity_id, 1 level , cast('/' + cast(related_id as varchar(1000)) + '/' as varchar(1000)) path from _entity_relation r where not exists(select null from _entity_relation r2 where r2.related_id=r.entity_id) or r.related_id is null -- create recursive path union all select r.id, r.entity_id, r.related_id, tab.level+1 , cast(tab.path + '/' + cast(r.entity_id as varchar(100)) + '/' + '/' + cast(r.related_id as varchar(1000)) + '/' as varchar(1000)) path from _entity_relation r join tab on tab.related_id = r.entity_id ) select x.id , x.entity_id ,pr.description as relation_description ,pt.first_name + coalesce(' ' + pt.middle_name,'') + ' ' + pt.last_name as relation_name ,CONVERT(CHAR(10), pt.birth_date, 101) as relation_birth_date from ( select entity_id, MAX(id) as id from ( select distinct tab.id, entity_id from tab join( select path from tab where entity_id=@in_entity_id ) p on p.path like tab.path + '%' or tab.path like p.path + '%' union select distinct tab.id, related_id from tab join( select path from tab where entity_id=@in_entity_id ) p on p.path like tab.path + '%' or tab.path like p.path + '%' union select distinct tab.id, entity_id from tab join( select path from tab where related_id=@in_entity_id ) p on p.path like tab.path + '%' or tab.path like p.path + '%' union select distinct tab.id, related_id from tab join( select path from tab where related_id=@in_entity_id ) p on p.path like tab.path + '%' or tab.path like p.path + '%' ) y group by entity_id ) x join _entity_relation pr on pr.id = x.id join _entity pt on pt.id = x.entity_id where x.entity_id <> @in_entity_id; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload