Link all IDs from associative entity that have common ID

Question

ERD just for ease to vizualise:

Locations from table "Location" can be linked to each other via the associative entity "Link Location"

Let's say that there are some current links that look like this:

location_id_1       location_id_2       active
1                   5                   True
5                   3                   True
2                   6                   True
4                   6                   True
6                   7                   True

I am trying to write a query that will return a single column with all IDs that might be connected to each other even if removed/distanced by one or more links. So, 1 is linked to 5 and 3 is linked to 5. Because of the common ID of 5, 1 is also linked to 3 once removed.

So, in my query I'd like to be able to decide on a "Prime Location", if you will, and then it will return all location ids, in one column, that are connected to my prime location, be it directly, or once or twice or n times removed.

I can do this easily with the 1st degree of links that might happen (See query below), but once I introduce 2nd or 3rd degree links, I am struggling to see another way other than manually updating my query to allow for another degree of linking.

declare  @PrimeLocation int
set @PrimeLocation = 1


Select location_id_1
from [Link Location]
where location_id_1 = @PrimeLocation
or location_id_2 = @PrimeLocation

union 

Select location_id_2
from [Link Location]
where location_id_1 = @PrimeLocation
or location_id_2 = @PrimeLocation

This query obviously only returns "1" and "5". But how do I get it to return "3" as well, and other IDs, should I add another link maybe to 3 in the future that might then be twice removed from 1? And can I do this without having to add to my query every time?

So, if my "Prime Location" = 1 (or 3 or 5) my result set should be:

location_id
1
3
5

And if my "prime location" is 2 (or 4 or 6 or 7) my result set should be:

location_id
2
4
6
7

Thanks in advance.

This looks like a recursive CTE would be necessary, I'm fairly certain a nearly identical version of this question was posted in the last few months; it might be worth your time to try some different search keywords to see if you can locate it. — Anthony Hancock, Feb 24 '17 at 21:10
[This](http://stackoverflow.com/a/15081353/92546) answer demonstrates one way of wandering through relationships recursively while avoiding infinite loops. The trick is to keep track of where you've been and terminating those recursions. — HABO, Feb 24 '17 at 21:42

score 0 · Answer 1 · answered Feb 27 '17 at 18:05

Well, after a bunch of experimenting and reading up on recursive CTEs I finally figured something out that works for me.

A few notes:

I had to add the depth column so that I could cancel out of the loop once my depth reaches a certain level. This is still not ideal, as I'd like the recursion to cancel itself when it finishes, but I don't see how that is possible as I do not have a top level in my hierarchy. In other words, my links aren't child to parent links like all the CTE examples I've been able to find where eventually there is one location that no longer has a link.

Anyway, here is what I did:

 declare  @PrimeLocation int
 set @PrimeLocation = 1
 ;


 WITH LocationLinks AS
 (
 SELECT location_id_1, location_id_2, 0 as depth
 FROM [Link Location]
 WHERE location_id_1 = @PrimeLocation
 OR location_id_2 = @PrimeLocation

 UNION ALL

 SELECT T1.location_id_1, T1.location_id_2, T2.depth + 1
 FROM [Link Location] T1
 JOIN LocationLinks T2  on T1.location_id_1 = T2.location_id_2 
 or T1.location_id_2 = T2.location_id_1
 WHERE T2.depth <=4

 )

 SELECT *
 INTO #Links
 FROM LocationLinks
 ------Lumping everything into one column-------
 SELECT distinct  location_id_1 
 FROM
 (
     SELECT distinct location_id_1
     FROM #Links

     UNION ALL

     SELECT distinct location_id_2
     FROM #Links
 ) a
 ORDER BY location_id_1

This results in my expected output of:

 location_id_1
 1
 3
 5

score 0 · Accepted Answer · answered Feb 27 '17 at 20:26

Assuming that the order of id's within pairs has no significance, this will produce the desired results:

-- Sample data.
declare @LinkLocations as Table ( LocationId1 Int, LocationId2 Int );
insert into @LinkLocations ( LocationId1, LocationId2 ) values
  ( 1, 5 ), ( 5, 3 ), ( 2, 6 ), ( 4, 6 ), ( 6, 7 );
select * from @LinkLocations;

-- Search the links.
declare @PrimeLocationId as Int = 1;
with Locations as (
  select @PrimeLocationId as LocationId,
    Cast( '.' + Cast( @PrimeLocationId as VarChar(10) ) + '.' as VarChar(1024) ) as Visited
  union all
  select LL.LocationId1,
    Cast( '.' + Cast( LL.LocationId1 as VarChar(10) ) + L.Visited as VarChar(1024) )
    from @LinkLocations as LL inner join
      Locations as L on L.LocationId = LL.LocationId2
    where L.Visited not like '%.' + Cast( LL.LocationId1 as VarChar(10) ) + '.%'
  union all
  select LocationId2,
    Cast( '.' + Cast( LL.LocationId2 as VarChar(10) ) + L.Visited as VarChar(1024) )
    from @LinkLocations as LL inner join
      Locations as L on L.LocationId = LL.LocationId1
    where L.Visited not like '%.' + Cast( LL.LocationId2 as VarChar(10) ) + '.%' )
  select LocationId -- , Visited
    from Locations
    option ( MaxRecursion 0 );

You can uncomment Visited in the last select to see some of the internals. This will correctly handle even degenerate cases like 42, 42 that link one id to itself.

Link all IDs from associative entity that have common ID

2 Answers2

Linked