How to get data from table where query condition related to query selected records

Context

I have the table "user_numbers" with the following records:

I want to build query that find all user numbers and all user numbers that users have.

For example above I want to get user's number 3, 4, 5, 7 and 8 if i will select by the next condition user_id = 3.

For user 3 i got numbers:

• 123
• 666

For number 123 i got users:

• 4
• 7

For number 666 i got usres:

• 5

User 7 has numbers:

• 123
• 444

For number 444 i got users:

• 8

In result i got all user numbers linked by number:

I create the next query:

WITH RECURSIVE founded_user_numbers AS (

    select
        un.id,
        un.user_id,
        un.number
    from p2p_market.user_numbers un
    where un.user_id = 1

    UNION

    select
        iun.id,
        iun.user_id,
        iun.number
    from p2p_market.user_numbers iun, founded_user_numbers fun
    where iun.number = fun.number and iun.id != fun.id and iun.user_id != fun.user_id
)

SELECT * FROM founded_user_numbers;

It works fine. Buy if i change UNION to UNION ALL or add order by i get infinity query.

Can anyone help me to write right condition to get required data.

From your dataset and results, it looks like, given a user id, you want all users that have a least one number in common. I don't see the need for recursion here. Here is one way to do it with boolean window functions:

select user_id
from (
    select n.*, 
        bool_or(user_id = 3) over(partition by number) number_has_user_id_3
    from user_numbers n
) n
group by user_id
having bool_or(number_has_user_id_3)
order by user_id

number_has_user_id_3 checks if user 3 has the same number as the one on the current row - which we can then use to filter users.

You could also use a correlated subquery (although that might be a less efficient approach):

select user_id
from user_numbers n
where exists (select 1 from user_numbers n1 where n1.number = n.number and n1.user_id = 3)
group by user_id
order by user_id

fiddle

I'm not really sure if this is what you are looking for, but I think you can achieve this by simply using a self-join.

select distinct test.t1_user_id from
  (select
     t1.id as t1_id
    ,t1.user_id as t1_user_id
    ,t1.number as t1_number
    ,t2.user_id as t2_user_id
    ,t2.number as t2_number
  from test t1

  left join test t2
    on t1.number = t2.number
    and t1.user_id <> t2.user_id

  where t2.user_id is not null) test

SQL Fiddle

with cte as
(
select *
from user_numbers
where user_id = 3
)
select distinct un.user_id from cte c
,user_numbers un
where un.number in (c.number)