连接3个mysql表 [英] Joining 3 mysql tables

问题描述

要读取和输出有关我们的网络许可证的信息，我每隔几分钟运行一个脚本，该脚本调用一个命令行实用程序，并将信息转储到3个mysql表中.现在，我想获取用户所属的活动会话和组名(如果用户是组的成员).

To read and output information about our network license I run a script every few minutes which calls a command line utility and dump the information in 3 mysql tables. Now I would like to get the active sessions and group name where the user belongs to (if the user is member of a group).

我使用的3个表是:

表:lm_session

Table: lm_session

• 功能ID(保存会话连接到的功能ID)
• 用户名(使用许可证的人)
• 工作站(系统已连接到许可证)
• 在(启动软件的日期)
• 退出(关闭软件的日期)
• 密钥(会话ID)
• 有效(是/否)

表lm_group

• 功能ID(保存该组连接到的功能ID
• 名称(组名)
• 许可证(该组的许可证数量)

表lm_group_user

Table lm_group_user

• Group_id(用户连接到的组ID)
• Feature_id(功能ID *可能不必要)
• 用户名(用户名)

此刻我有这样的查询

SELECT s.*, g.* FROM lm_session AS s 
LEFT JOIN lm_group_user AS u ON s.user = u.user 
LEFT JOIN lm_group AS g ON g.id = u.group_id 
WHERE s.feature_id = :featureid AND s.active = 1

我确实得到了所有具有活动会话的用户，但是找不到正确的组名.如果用户是多个组的成员，则s.user = u.user仅找到第一个组.结果不是基于s.user = u.user和g.id = u.group_id的组合

I do get all the users that having an active session, but the name of the group is not found properly. If a user is member of multiple groups, the s.user = u.user only finds the first group. The result is not based on a combination of s.user = u.user AND g.id = u.group_id

有人可以帮助我吗?

推荐答案

尝试在下面运行查询:

SELECT
s.Username, 
s.Featuredid,
g.Name AS groupname

FROM lm_session s 
JOIN lm_group_user gu ON s.Username = gu.Username
LEFT JOIN lm_group g ON s.Featuredid = g.Featuredid

WHERE s.Active = 1

可选: 添加 GROUP BY a.Username (如果用户在lm_session表中具有多个会话，则可能需要添加此行以避免重复，但是您可以开始运行此查询而不会看到发生了什么.由于我一直在考虑一个用户在不同的软件中可能具有多个会话)

Optional: Add GROUP BY a.Username (If an user have multiple session in the lm_session table you might want to add this line to avoid duplicates, but you can start running this query without it see what happens. Since I was thinking an user might have multiple session in different software )

这篇关于连接3个mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！