CODEWITHSUNDEEP
postgresql
MCM13
MCM13

Reputation: 275

PostgreSQL - Select value depending on other table's value

I have two tables, one called "users" other called "referral". Every user has a code associated with it and this is how they look:

USERS TABLE

user_id  name    ref_type   other_referral
 1       Ely      friend         SA0987
 2       Sandra   page  
 3       Karla    friend         EC0000
 4       Tania    email 

REFERRAL TABLE

user_id   code
  1      EC0000
  2      SA0987

I want to select everything from the users table, but if the referral matches with friend and other_referral is not empty, then I need instead of that alpha numerical code, I want the name of the user, which can be associated from the table REFERRAL.

EXAMPLE:

id  name    ref_type    other_referral
1   Ely      friend        Sandra
2   Sandra   page   
3   Karla    friend         Ely
4   Tania    email

Upvotes: 0

Views: 36

Answers (1)

xehpuk
xehpuk

Reputation: 8250

LEFT JOIN from users via referral back to users to find the friend.

SELECT u.user_id AS id, u.name, u.ref_type, coalesce(f.name, u.other_referral) AS other_referral
FROM users u
LEFT JOIN referral r
  ON u.ref_type = 'friend'
    AND u.other_referral = r.code
LEFT JOIN users f
  ON r.user_id = f.user_id
ORDER BY id;

I have added a fallback to the alphanumeric code via coalesce in case there's not a friend with that code or there is a different ref_type which can have a code, too.

Here is the db<>fiddle.

Upvotes: 1

Related Questions