PostgreSQL - Select value depending on other table's value

Question

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

xehpuk · Accepted Answer · 2020-11-30 19:01:03Z

1

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.

answered Nov 30, 2020 at 19:01

xehpuk

8,3804 gold badges37 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MCM13 Over a year ago

This is exactly what I needed, thank you for your help!! :)

Collectives™ on Stack Overflow

PostgreSQL - Select value depending on other table's value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related