如果字段不为空，则使用MYSQL JOIN [英] MYSQL JOIN if field is not null

问题描述

我想通过读取一个整个表从数据库中获取数据，再连接另一个和一个表，但只有最后一个表中的指定字段不为NULL时，才连接最后一个表.这是查询:

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`) 

应该可以，但是不能.如果updaterid不为NULL，它将返回有效结果，但如果为null，我将不断获得空结果.关于这个有什么想法吗?

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我还尝试了一些与此相关的组合，例如使用WHERE或其他各种JOIN.

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

推荐答案

如果我对您的理解正确(您没有提供任何示例数据)，则此查询将提供您想要的数据:

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`) 

如果a.updaterid IS NULL`(或删除了更新程序用户)，则

u2.Name将为NULL，否则将保留名称.

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

这篇关于如果字段不为空，则使用MYSQL JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！