0

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,   mike@gmail.com
    3000,   joe@gmail.com
    4000,   jack@gmail.com

accounts

   [account_id ,    email]
   120,          maria@gmail.com
   155,         travis@gmail.com

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

0

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;
| improve this answer | |
  • thanks @SQLRaptor it is working perfectly , the error that i was getting the email (because we have email from users and accounts) but the COALESCE worked to solve the problem :-) – Medo May 28 '18 at 15:14
  • could you please explain what do you mean by also look at CASE for non null condition predicates. thanks – Medo May 28 '18 at 15:46
  • Just look in the link I mentioned, it explains CASE - postgresql.org/docs/9.1/static/functions-conditional.html. COALESCE is a conditional expression for NULL predicates - it returns the first non null expression. CASE is a more general purpose tool, and is extremely useful in other cases. Since you were not familiar with conditional expressions, I just thought it is something worth mentioning. You could replace the COALESCE with something like "... CASE WHEN accounts.email IS NULL THEN professionals.email ELSE accounts.email END AS Email ... " – SQLRaptor May 28 '18 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.