How to select all from table one based on a value, then select from same table with value from that table?

Question

I have a table with users that can look like this. Where the Socialnr is empty in one or more rows.

Userid:1 Phone:12345 Email:test@test.com Socialnr:88776655
Userid:2 Phone:12345 Email:              Socialnr:
Userid:3 Phone:      Email:test@test.com Socialnr:

Lets say I only have the Socialnr from the start. So from this I need to

select * from users where socialnr=88776655

but then I need to be able to select the two other rows as well based on the same email or phonenumber that I get from the first select. I need separate rows. Any input appreciated, thanks.

Based on Gordons answer I have this now with my real code.

SELECT u.* FROM appmanager.appusers u where u.userId='797' and u.personnrvh2='88776655' OR exists (select 1 from appmanager.appusers u2 where u2.userId='797' and u2.personnrvh2='88776655'  and (u2.emailvh2 = u.emailvh2 or u2.mobilvh2 = u.mobilvh2))

Ok so this was the solution, thanks a lot Gordon!

Answer 1

Hmmm . . . based on your description, you can use exists:

select u.*
from users u
where u.Socialnr = 88776655 or
      exists (select 1
              from users u2
              where u2.Socialnr = 88776655 and
                    (u2.email = u.email or u2.phone = u.phone)
             );

If you wanted, you could also use window functions . . . assuming that each email and phone has one ultimate Socialnr

select u.*
from (select u.*,
             max(Socialnr) over (partition by email) as imputed_socialnr_email,
             max(Socialnr) over (partition by phone) as imputed_socialnr_phone
      from users u
     ) u
where 88776655 in (Socialnr, imputed_socialnr_email, imputed_socialnr_phone)