CODEWITHSUNDEEP
mysql
A_nik
A_nik

Reputation: 15

MySQL if matching id not found in another table

Users
+----+------+
| id | name |
+----+------+
| 1  | John |
+----+------+
| 2  | Jane |
+----+------+

Message
+----+------+--------------+
|u_id| date | msg          |
+----+------+--------------+
| 1  | 0945 | some text1   |
+----+------+--------------+
| 0  | 0950 | some Text2   |
+----+------+--------------+
| 2  | 1045 | some Text3   |
+----+------+--------------+

I want 'not_registered_user' text as a name of User where user id is 0 (it is every time 0 for a nonregister user). The desired output would be:

+------+--------------+--------------+
| date | name         | msg          |
+------+--------------+--------------+
| 0945 | John         | some text1   |
+------+--------------+--------------+
| 1045 | Jane         | some text3   |
+------+--------------+--------------+
| 0950 | non_reg_user | some text2   |
+------+--------------+--------------+

Help please, Thanks in Advance.

Upvotes: 1

Views: 39

Answers (1)

Fahmi
Fahmi

Reputation: 37473

Use left join and coalesce()

select date,coalesce(name,'non_reg_user') as name,msg
from message a
left join users b on a.u_id=b.id

Upvotes: 2

Related Questions