If else statement on join query

Question

i am working with a Postgresql database

having two tables: professionals[professional_id,user_id,account_id]

users[user_id email]

accounts[account_id email]

professionals

 [professional_id,user_id,account_id]

    1,          2000,       null
    2,          3000,        120
    3,          4000,        155

users

   [user_id,   email]
    2000,   [email protected]
    3000,   [email protected]
    4000,   [email protected]

accounts

   [account_id ,    email]
   120,          [email protected]
   155,         [email protected]

Here i have to get the email with doing a join between accounts and professionals.

If account_id is not null we get the email

If the account_id is null we do the join between the users and professionals (we are sure that user_id is always not null)

Here is what i did :

select email from professionals  
IF (u.id=m.user2ID)
left JOIN accounts ON accounts.account_id = professionals.account_id
ELSE
LEFT JOIN users  ON users.user_id = professionals.user_id;

Thanks indeed for any help

Answer 1

UNTESTED - You did not provide any DDL or sample data scripts... IF is a flow control construct that determines which statement block will be executed. What you need are Conditional Expressions. In your case, the COALESCE function should do the trick, also look at CASE for non null condition predicates.

SELECT COALESCE(accounts.email, professionals.email) AS Email 
FROM professionals  
LEFT JOIN accounts ON accounts.account_id = professionals.account_id
LEFT JOIN users  ON users.user_id = professionals.user_id;