MYSQL JOIN if field is not null

Question

I want to get data from database by reading one whole table, join another one and one more but the last one only if the specified field in first isn't NULL. Here's the query:

SELECT a.*, u1.Name, u2.Name FROM `articles` a
JOIN `users` u1 ON (a.id=1 AND u1.`id` = a.`authorid` )
JOIN `users` u2 ON (a.`updaterid` IS NOT NULL AND u2.`id` = a.`updaterid`) 

Should work okay but it does not. It returns valid result if updaterid isn't NULL but if it is, I keep getting empty result. Any ideas on this one?

PS I also tried some combinations around this e.g. with WHERE or different kinds of JOINs.

Solution 1

If I understand you correctly (you didn't provide any examlpe data), this query provides the data you want:

SELECT a.*, u1.Name, u2.Name FROM `articles` a
JOIN `users` u1 ON (a.id=1 AND u1.`id` = a.`authorid` )
LEFT JOIN `users` u2 ON (a.`updaterid` IS NOT NULL AND u2.`id` = a.`updaterid`) 

u2.Name will be NULL if a.updateridIS NULL` (or the updater-user is deleted) or will hold the name otherwise.

Solution 2

Make both joins outer joins.

SELECT a.*, u1.Name, u2.Name 
FROM `articles` a
   Left JOIN `users` u1 
      ON u1.`id` = a.`authorid`
        AND a.id=1
   left JOIN `users` u2 
      ON  u2.`id` = a.`updaterid`
         AND a.`updaterid` IS NOT NULL 

and once you do that you probably don't need the is not null condition...

SELECT a.*, u1.Name, u2.Name 
FROM `articles` a
   Left JOIN `users` u1 
      ON u1.`id` = a.`authorid`
        AND a.id=1
   left JOIN `users` u2 
      ON  u2.`id` = a.`updaterid`