MySQL: Where clause base on result from IF statement?

Question

Here is the query:

select IF ((t1.emp_id IS NOT NULL), t2.username, '') 
from t1, t2 
where t1.user_id = "285" AND t2.id = t1.emp_id

It works fine, but if t1.emp_id is NULL it will not return result because of this part AND t2.id = t1.emp_id but this part secure me that I will get exact t2.id if t1.emp_id is not NULL. Is there a way to handle it ?

The idea is if t1.emp_id is not null then to return t2.username based on relation t1.emp_id = t2.id but in this case when t1.emp_id is null it doesn't return result, because of last part of query. If I remove last part it will not returns me exact row from t2.

Sample data, desired results, and an explanation of the logic would all help. Why are you not using proper, explicit, standard, readable JOIN syntax. — Gordon Linoff
– Gordon Linoff, Commented Jun 12, 2020 at 13:59

Gordon Linoff · Accepted Answer · 2020-06-12 14:03:01Z

1

I think you want a LEFT JOIN. My best guess is:

select coalesce(t2.username, '')
from t1 left join
     t2 
     on t2.id = t1.emp_id 
where t1.user_id = 285;

answered Jun 12, 2020 at 14:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Kaumadie Prabashinie · Accepted Answer · 2020-06-12 14:05:46Z

0

use following query & try again.

select t1.emp_id, t2.username
from t1, t2 
where t1.user_id = "285" AND t2.id = t1.emp_id AND t1.emp_id is NOT NULL;

answered Jun 12, 2020 at 14:05

Kaumadie Prabashinie

1,5063 gold badges23 silver badges40 bronze badges

1 Comment

Sahin Urkin Over a year ago

This will not work, because I want to return results, whether or not there is t1.emp_id

Collectives™ on Stack Overflow

MySQL: Where clause base on result from IF statement?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related