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.

Advertisement

Answer