I have two tables with me. Say one has list of actions (the original table that I work on has a list of messages sent and received) and the other is a user table, with the full name of the users. I have replicated an issue of what I have right now.
The users found in the actions table (messages table) may not be present in the users table. I am using a subquery to select the full name of the users against the username in the original query for the actions table. When the user in the actions table is not present in the users table, MySQL returns a NULL and this is expected. But instead, I want it to return the original username.
Say, praveen is there both in the users table as well as actions table. So, I get something like this: Praveen Kumar likes something.. Say, I have another username, bluff that is not present in the users table, and the action becomes this way: NULL shares something.. Here instead of NULL, is it possible to return the username itself, like bluff shares something.?
My SQL Fiddle, schema details and other references have been provided here. Let me know if I can do it in a better way. Thanks.
MySQL 5.5.30 Schema Setup
CREATE TABLE `users`
    (`username` varchar(15), `fullname` varchar(15))
;
INSERT INTO `users`
    (`username`, `fullname`)
VALUES
    ('praveen', 'Praveen Kumar'),
    ('jeff', 'Jeff Atwood')
;
CREATE TABLE `actions`
    (`user` varchar(7), `action` varchar(7))
;
INSERT INTO `actions`
    (`user`, `action`)
VALUES
    ('praveen', 'Like'),
    ('jeff', 'Comment'),
    ('praveen', 'Answer'),
    ('praveen', 'Share'),
    ('jeff', 'Comment'),
    ('bluff', 'Share'),
    ('jeff', 'Like')
;
Query
SELECT *, (
  SELECT `fullname` FROM `users` WHERE `user` = `username`
) AS `name` FROM `actions`
Result
+---------+---------+---------------+
| USER    | ACTION  | NAME          |
+---------+---------+---------------+
| praveen |    Like | Praveen Kumar |
|    jeff | Comment | Jeff Atwood   |
| praveen |  Answer | Praveen Kumar |
| praveen |   Share | Praveen Kumar |
|    jeff | Comment | Jeff Atwood   |
|   bluff |   Share | (null)        |
|    jeff |    Like | Jeff Atwood   |
+---------+---------+---------------+
Expected Output
+---------+---------+---------------+
| USER    | ACTION  | NAME          |
+---------+---------+---------------+
| praveen |    Like | Praveen Kumar |
|    jeff | Comment | Jeff Atwood   |
| praveen |  Answer | Praveen Kumar |
| praveen |   Share | Praveen Kumar |
|    jeff | Comment | Jeff Atwood   |
|   bluff |   Share | bluff         |
|    jeff |    Like | Jeff Atwood   |
+---------+---------+---------------+
Use IFNULL
SELECT *, IFNULL((
  SELECT `fullname` FROM `users` WHERE `user` = `username`
),`user`) AS `name` FROM `actions`
SQLFiddle
EDIT:
Also COALESCE does the same
SELECT *, COALESCE((
  SELECT `fullname` FROM `users` WHERE `user` = `username`
),`user`) AS `name` FROM `actions`
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With