mysql - 有多个外键的表在MySQL中加入1个外键

嗨，我有一个包含以下信息的数据库表：

owner.primaryitowner, ([email protected])
owner.secondaryitowner,
owner.primarybusinessowner,
owner.secondarybusinessowner
users.username (email [email protected])
users.displayname (e.g. Bob Smith)

问题是，车主只会被储存为电子邮件。我通常还有一张桌子
inner join users on users.username = owner.primaryitowner to get users.displayname
所以数据读取正确。`
我能做到

select u.displayname
from users u
inner join owners o on
    o.primaryitowner = u.username
    or o.secondaryitowner = u.username
    or o.primarybusinessowner = u.username
    or o.secondarybusinessowner = u.username

问题是，我需要有唯一的列不是所有列成一列。
PS我不能更改数据库我只是一个报表作者。
提前谢谢你

最佳答案

您将希望将用户的每一列电子邮件加入所有者

SELECT u.displayname AS userName
   , po.displayName AS PrimaryItOwnerUsernName
   , so.displayName AS SecondaryIdOwnerUserName
FROM users AS u
INNER JOIN owners AS po on u.primaryitowner = po.username
INNER JOIN owners AS so ON u.secondaryitowner = so.username
...
WHERE u.UserName = 'Ryan J Morse'

当您多次加入所有者表（化名）时，这允许您将存储在用户中的电子邮件更改为报表所需的显示名称。

关于mysql - 有多个外键的表在MySQL中加入1个外键，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13076565/