sql - Need query to select direct and indirect customerID aliases -

i need query return related alias id's either column. shown here alias customer ids, among thousands of other rows. if input parameter query id=7, need query return 5 rows (1,5,7,10,22). because aliases of one-another. example, 22 , 10 indirect aliases of 7.

customeralias -------------------------- aliascuid   aliascuid2  -------------------------- 1           5       1           7       5           7       10          5       22          1       

here excerpt customer table.

customer ---------------------------------- cuid    cufirstname    culastname ----------------------------------  1      mike           jones  2      fred           smith  3      jack           jackson  4      emily          simpson  5      mike           jones  6      beth           smith  7      mike           jones  8      jason          robard  9      emilie         jiklonmie  10     michael        jones  11     mark           lansby  12     scotty         slash  13     emilie         jiklonmy  22     mike           jones 

i've been able come close, cannot seem select indirectly related aliases correctly. given query:

select distinct customer.cuid, customer.cufirstname, customer.culastname  customer     (customer.cuid = 7) or (customer.cuid in   (select aliascuid2     customeralias customeralias_2     (aliascuid = 7))) or (customer.cuid in   (select aliascuid    customeralias customeralias_1   (aliascuid2 = 7))) 

returns 3 out of 5 of desired ids of course. lacks indirectly related aliased id of 10 , 22 in result rows.

1   mike    jones 5   mike    jones 7   mike    jones 

* based on suggestions below, trying cte hierarchical query.

i have after following suggestions. works some, long records in table reference enough immediate ids. but, if query uses id=10, still comes short, nature of data.

declare @id int set @id = 10;   declare @tmp table ( a1 int, a2 int, lev int );  results (aliascuid, aliascuid2, [level]) (    select aliascuid,           aliascuid2,           0 [level]      customeralias     aliascuid = @id or aliascuid2 = @id    union    -- recursive step    select a.aliascuid,           a.aliascuid2,           r.[level] + 1 [level]      customeralias      inner join results r on a.aliascuid = r.aliascuid2 )      insert @tmp         select * results;   results3 (aliascuid, aliascuid2, [level]) (    select aliascuid,           aliascuid2,           0 [level]      customeralias     aliascuid = @id or aliascuid2 = @id    union    -- recursive step    select a.aliascuid,           a.aliascuid2,           r.[level] + 1 [level]      customeralias      inner join results3 r on a.aliascuid2 = r.aliascuid )      insert @tmp         select * results3;     select distinct a1 id @tmp   union   select distinct a2 id @tmp   order id 

note simplified query give list of related ids.

--- id --- 5 5 7 10 

but, still unable pull in ids 1 , 22.

this not easy problem solve unless have idea of depth of search (https://stackoverflow.com/a/7569520/1803682) - looks not - , take brute force approach it.

assuming not know depth need write stored proc. followed approach identical problem: https://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161#7161

update if don't care chain of how alias's created - run script recursively make them refer single (master?) record. can search , quick - not solution if care how alias's traversed though.