MySQL if else statement inside query

Question

I have this inner query that selects all affiliates linked to the giver user.

 (SELECT 
    `realtor_user_id`
 FROM
    `affiliate_assignments`
 WHERE
 `lender_user_id` = `users`.`id`)

affiliate_assignments structure is:

id | realtor_user_id | lender_user_id

where users table is joined in outer query and both realtor_user_id and lender_user_id are foreign keys of users.id

I also have user's role (selected in outer query) that can be realtor or lender

What I need is:

IF role = 'lender'

 SELECT realtor_user_id 
 FROM affiliate_assignments
 WHERE lender_user_id = users.id

ELSE IF role = 'realtor'

 SELECT lender_user_id
 FROM affiliate_assignments
 WHERE realtor_user_id = users.id

END IF

How can achieve this?

Use CASE in place of IF. https://dev.mysql.com/doc/refman/5.7/en/case.html — Syntax Error, Nov 24 '16 at 15:06
This is definitely not a duplicate of a question that just says the answer is `case`. Read the question . . . it involves multiple different columns and correlations. — Gordon Linoff, Nov 24 '16 at 15:15

ScaisEdge · Answer 1 · 2016-11-24T15:35:22.803

2

You can use case when

    select case when role = 'lender' then realtor_user_id 
                when role = 'realtor'  then lender_user_id 
            end  as my_result 
    FROM affiliate_assignments
    WHERE lender_user_id = users.id

do the fact you have different where condition you can rebuil you condition using composite condition is when clause

select  case  
            when role = 'lender'  AND lender_user_id = users.id  then realtor_user_id 
            when  role = 'realtor' AND realtor_user_id = users.id then lender_user_id
                  ELSE  your_result_for else 
FROM affiliate_assignments

edited Nov 24 '16 at 15:35

answered Nov 24 '16 at 15:09

ScaisEdge

131,976
10
91
107

No need for a case in the where clause? – jarlh Nov 24 '16 at 15:13
OP's pseudo-code has `WHERE lender_user_id = users.id` in the first select, and `WHERE realtor_user_id = users.id` in the second. – jarlh Nov 24 '16 at 15:16
Yeah, what about the where clause? – xpuc7o Nov 24 '16 at 15:24

score 0 · Answer 2 · answered Nov 24 '16 at 15:12

0

Using case statement:

SELECT CASE
    WHEN role = 'lender' THEN realtor_user_id
    WHEN role = 'realtor' THEN lender_user_id
    END CASE 
FROM affiliate_assignments
WHERE realtor_user_id = users.id

answered Nov 24 '16 at 15:12

d345k0

78
1
6

score 0 · Answer 3 · answered Nov 24 '16 at 15:17

One way to approach this uses left joins:

select u.*,
       coalesce(aal.realtor_user_id, aar.lender_user_id) as other_user_id
from users u left join
     affiliate_assignments aal
     on u.id = aal.lender_user_id and u.role = 'lender' left join
     affiliate_assignments aar
     on u.id = aar.realtor_user_id and u.role = 'realtor';

MySQL if else statement inside query

3 Answers3