Ozzy Ozzy - 2 years ago 95
SQL Question

Mysql Left Join Null Result

I have this query

SELECT articles.*,
users.username AS `user`
FROM `articles`
LEFT JOIN `users` ON articles.user_id = users.id
ORDER BY articles.timestamp

Basically it returns the list of articles and the username that the article is associated to. Now if there is no entry in the users table for a particular user id, the
var is NULL. Is there anyway to make it that if its null it returns something like "User Not Found"? or would i have to do this using php?

Answer Source


   SELECT a.*, 
          COALESCE(u.username, 'User Not Found') AS `user` 
LEFT JOIN USERS u ON u.id = a.user_id
 ORDER BY articles.timestamp 


The reason to choose COALESCE over IF or IFNULL is that COALESCE is ANSI standard, while the other methods are not reliably implemented over other databases. I would use CASE before I'd look at IF because again - CASE is ANSI standard, making it easier to port the query to other databases.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download