php - 条件连接问题，如果一个字段为空，请选择其他值进行连接

Question

我有一个问题。我想在“用户”表上加入“公司”表。问题是，用户可以是公司的作者/创建者，也可以是成员。

这意味着在第一种情况下，字段 users.company 为空，但字段 Companies.authorID 填充有用户 ID 或第二种情况，users.company 填写公司 id，companys.authorID 为空。

如何将公司表连接到用户表？

应该是这样的

LEFT JOIN companies ON companies.id = users.company AND IF users.company = '0' THEN ON companies.authorID = users.id

有什么办法可以在 join 子句中实现我想要的效果吗？

非常感谢!

具有完整子句的当前状态(使查询崩溃)

SELECT 
count(users.id),
companies.city AS selectVariable
FROM users 
LEFT JOIN companies ON companies.id = users.company OR (users.company = '0' AND companies.authorID = users.id)
WHERE users.email <> '' AND users.deleted = '0'
GROUP BY companies.city
ORDER BY count(users.id) DESC
LIMIT 20 OFFSET 0

这个解决方案有效，但是确实如此。没有我希望的那么干净......

SELECT 
count(users.id),
    (
    CASE 
        WHEN users.company = '0' THEN c2.city
        WHEN users.company > '0' THEN c.city
        ELSE NULL
    END) AS selectVariable
FROM users 
LEFT JOIN companies c ON c.id = users.company
LEFT JOIN companies c2 ON c2.authorID = users.id
WHERE users.email <> '' AND users.deleted = '0'
GROUP BY selectVariable
ORDER BY count(users.id) DESC
LIMIT 20 OFFSET 0

Answer 1

是的，您可以在加入中使用或条件:

LEFT JOIN companies c ON c.id = u.company 
           OR (u.company = '0' AND c.authorID = u.id)