我有一个问题。我想加入"用户"表中的"公司"表。问题是,用户既可以是公司的作者/创建者,也可以是公司成员。
这意味着在其中一种情况下,字段users.company为空,但字段companies.authorID填充有用户id或第二种情况是,users.company填充了公司id,companies.authoriID为空。
那么,我如何将公司表加入用户表?
应该是类似的东西
LEFT JOIN companies ON companies.id = users.company AND IF users.company = '0' THEN ON companies.authorID = users.id
有什么方法可以在联接条款中实现我想要的吗?
非常感谢!
带有完整子句的当前状态(使查询崩溃)
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
这个解决方案是有效的,但def.没有我希望的那么干净…
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
是的,您可以在join
:中使用or
标准
LEFT JOIN companies c ON c.id = u.company
OR (u.company = '0' AND c.authorID = u.id)