Question 189748 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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:

id user_id number
1 3 123
2 3 666
3 4 123
4 5 666
5 6 555

I want to build query that find all user numbers and all user’s numbers that have the same numbers.

For example above I want to get users 3, 4 and 5 if i will select by the next condition user_id = 3.

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.

Tags:

2

Answers


  1. 0

    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
    user_id
    3
    4
    5

    fiddle

    Login or Signup to reply.
  2. 0

    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.

    SQL Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search