用一个公共字段联接MySql中的表，但公共字段的值不同 [英] Joining tables in MySql with one common field but different values for the common field

问题描述

如何将3个表与一个公共字段连接起来

how to join 3 tables with one common field like

• 表1:c_id，用户名，密码，c_role
• 表2:p_id，用户名，密码，c_role
• table3:s_id，用户名，密码，c_role

在此 c_role 字段中很常见 在这里，我分配了

in this c_role field is common here i assigned

• 表1中c_role的枚举'1'
• 表2中c_role的枚举'2'
• 表3中c_role的枚举'3'

用于授予3个不同用户的权限，例如corres，principal和登录时的工作人员. 现在，当他们登录时，它应该可以识别用户并将他们带到他们的页面.

for giving rights to 3 different users like corres, principal and for a staff when they login.. Now when they login it should identify the user and take them to their page..

推荐答案

除非c_id，p_id和s_id都具有相同的ID，否则唯一的连接将是用户名(和密码?).

The only joins would be on username (and password ?) unless c_id, p_id and s_id are all the same id?

除了每个表中的角色完全愚蠢之外，您无法加入角色，除了表1中的完全愚蠢的角色​​= 1等效于表2中的角色= 2.

You can't join on role seeing as they are different in each table, aside from an utterly daft role = 1 in Table1 is the equivalent of role = 2 in table2.

你是说工会吗?如您所愿

Do you mean a union? As in you want

User Password Role
Fred Fr3d     1
Fred ???      2
Fred ???      3

不确定您要使用这种模式实现什么，但是它违反了本书中的每条规则，似乎无法满足您的需求....

Not sure what you are trying to achieve with this schema, but it breaks near every rule in the book, and doesn't seem to meet your needs....

根据您的评论，您可能会看的一种方法.

Based on your comment, one way you might look at is.

是

Users (UserID, UserName, Password etc) Key UserID
Roles (RoleID, RoleName etc) Key RoleID
UserRoles(UserID,RoleID) Key UserID,RoleID

您需要学习一些有关数据库的知识，尤其是规范化，对于大多数事情，前三种形式应该适用.

You need to learn a bit about databases particularly normalisation, first three forms should do for most things.

然后

Select UserName, Password,RoleName From Users 
inner join UserRoles on Users.UserID = UserRoles.UserID
inner join Roles on UserRoles.RoleID = Roles.RoleId

之类的东西变得可能且高效.

and such like become possible and efficient.

这篇关于用一个公共字段联接MySql中的表，但公共字段的值不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！