sql - MySQL get linked results from the same table -

i can't wrap head around small (hopefully) mysql question. have table called links. contains customer_id field , linked_id field , links customer accounts each other customer_id in lead. newly created accounts can spawn accounts , see accounts created logged on user + accounts created subaccounts.

table looks this:

+----+-------------+-----------+ | id | customer_id | linked_id | +----+-------------+-----------+ |  1 |           1 |         5 | |  2 |           1 |         2 | |  3 |           1 |        11 | |  4 |           1 |        13 | |  5 |          13 |        14 | |  6 |           3 |         4 | |  7 |           7 |         8 | +----+-------------+-----------+ 

so if logged in user customer_id 1 userlist linked_id 5,2,11,13 (because direct connection) , linked_id 14 (because user created user directly connected 1).

the query needs subquery user details. have:

select username, firstname, lastname, email, active, level customers id in (select linked_id links customer_id=1) or id=1; 

this returns direct connections , user id=1 directly.

thanks eggyal putting me on right track. seeing relative complexity not feel ashamed anymore not crack in first go.

i ended doing research , found nice setups used closure tables in mysql. ended creating stored procedure populate closure table , of course new table cust_closure. renamed links table cust_links.

cust_links:

+-------------+---------+------+-----+---------+----------------+ | field       | type    | null | key | default |          | +-------------+---------+------+-----+---------+----------------+ | id          | int(11) | no   | pri | null    | auto_increment | | customer_id | int(11) | yes  |     | null    |                | | linked_id   | int(11) | yes  |     | null    |                | +-------------+---------+------+-----+---------+----------------+ 

cust_closure:

+-------------+---------+------+-----+---------+-------+ | field       | type    | null | key | default | | +-------------+---------+------+-----+---------+-------+ | customer_id | int(11) | yes  |     | null    |       | | linked_id   | int(11) | yes  |     | null    |       | | distance    | int(11) | yes  |     | null    |       | +-------------+---------+------+-----+---------+-------+ 

and added stored procedure:

create procedure populate_cust_closure() begin   declare distance int;   truncate table cust_closure;   set distance = 0;   -- seed closure self-pairs (distance 0)   insert cust_closure (customer_id, linked_id, distance)     select customer_id, customer_id, distance       cust_links group customer_id;    -- each pair (root, leaf) in closure,   -- add (root, leaf->child) base table   repeat     set distance = distance + 1;     insert cust_closure (customer_id, linked_id, distance)       select cust_closure.customer_id, cust_links.linked_id, distance         cust_closure, cust_links           cust_closure.linked_id = cust_links.customer_id           , cust_closure.distance = distance - 1;   until row_count()=0   end repeat; end //  

when called stored procedure produced:

mysql> select * cust_closure; +-------------+-----------+----------+ | customer_id | linked_id | distance | +-------------+-----------+----------+ |           1 |         1 |        0 | |           3 |         3 |        0 |  |           7 |         7 |        0 |  |          13 |        13 |        0 |  |           1 |         5 |        0 | |           1 |         2 |        0 | |           1 |        11 |        0 | |           1 |        13 |        0 | |          13 |        14 |        0 | |           1 |        14 |        1 | |           3 |         4 |        0 | |           7 |         8 |        0 | +-------------+-----------+----------+ 

so original query becomes:

select username, firstname, lastname, email, active, level customers id in (select linked_id cust_closure customer_id=1); 

thanks again eggyal , hope helps in future.