SQL JOIN and desired a view: 2 foreign keys referencing the same table

I feel I can’t solve my task without community help. Unfortunately, my knowledge of SQL is weak.

I have 2 tables – user and referral which represents data about senders and referral of senders (sender can have a 1..n referral, but referral can have only one sender).

create table user 
(
    id integer primary key,
    name text,
    email text
);

create table referral 
(
   id integer primary key,
   sender_id integer references user1 (id),
   referral_id integer references user1 (id),
   token text,
   unique (referral_id)
);

I would like to create 2 queries – get all referrals for the specific sender and other ones to get sender for the specific referral.

For getting all referrals:

SELECT user1.name AS "sender", referral.referral_id AS "referral"
FROM user1 
JOIN referral ON referral.sender_id = user1.id
WHERE referral.sender_id = requested sender id;

Output:

|  sender  | referral |
+----------+----------+
| John     |     5    |
|  ....    |     ...  |
|----------|----------|

For getting sender for the specific referral:

SELECT user1.name AS "sender", referral.referral_id AS "referral"
FROM user1 
JOIN referral ON referral.sender_id = user1.id
WHERE referral.referral_id = requested referral id;

Output:

|  sender  | referral |
+----------+----------+
| John     |     5    |

Everything is ok except referral output. I would like to view referral name instead of id.

Also I would like to get output for sender of specific referral only sender name, without the second column referral, because there is one-to-one relationship.

Could anyone to help me understand join in my case?!

Thank you in advance!

Answer

You want to join to users twice:

SELECT us.name AS sender, ur.name AS referral
FROM referral r LEFT JOIN
     user1 us
     ON r.sender_id = ur.id LEFT JOIN
     user1 ur
     ON r.referral_id = ur.id
WHERE r.sender_id = ?;

Notes:

• You need table aliases so you can refer the same table multiple times in the FROM clause.
• This logic uses LEFT JOIN, in case one of the columns is empty.
• You should be passing in the requested sender id using a parameter, which is why I’ve used ? as a parameter placeholder.