mysql - 连接多个(超过 3 个)表的顺序是什么以及在哪里放置条件？

Question

我已经做了相当多的阅读和测试，我可以从单独的查询中获取我想要的信息，但我似乎无法加入它们以将所有内容都放在一个表中。

目标 - 列出所有容器以及相关 block 和 AdminRole 的名称。

这是我需要的表和字段之间关系的可视化: Table Structure Picture

由于我还无法附加图片，因此以下是表格说明:

• 管理员ACL
  • adminID (=adminrole_admin.adminID)(编辑:=adminrole.ID)
  • objectID (=Container.ID)
  • ObjectType(条件“容器”)
• adminrole_admin(编辑:不需要表)
  • adminroleID (=adminrole.ID)
  • adminID (=AdminACL.adminID)
• 管理员角色
  • ID (=adminrole_admin.adminroleID)(编辑:=AdminACL.adminid)
  • 名称 <- 结果表中所需的字段[条件不类似于“hidden_​​%”的加分]
• 容器
  • ID (=AdminACL.objectID)
  • 名称 <- 结果表中所需的字段
• 容器 block
  • 容器ID (=Container.ID)
  • 区 block ID (=区 block .ID)
• 阻止
  • ID (=container_block.blockID)
  • 名称 <- 结果表中所需的字段

我为每个部分都有选择语句，但由于它会导致异常困惑的查询，因此我尝试了连接，但最终得到了空集。我不确定从哪个表开始以及是否需要使用不同类型的联接和/或以不同的顺序。

这是我放弃之前尝试的最后一个查询:

SELECT C.name, B.name, A.name 
FROM container C
JOIN (SELECT adminid,objected from adminacl where objecttype='Container') ACL ON ACL.objectid=C.id
JOIN adminrole_admin AA ON ACL.adminid=AA.adminid
JOIN (select id,name from adminrole where name not like 'hidden_%') A ON AA.adminid=A.id
JOIN container_block CB ON C.id=CB.containerid
JOIN block B ON CB.blockid=B.id;

尽管我很喜欢这个答案，但我也想了解将来如何构建此类查询。我很确定我错过了一些明显的东西 - 只是很难将我看过的所有其他示例与此联系起来。 TIA!

编辑:事实证明 adminacl.adminid=adminrole.id，这使事情变得更加简单。最终还需要“隐藏”管理角色，这些角色是在角色之上添加的基于特定用户的权限。根据 Russ 的回答，这就是我最终的查询:

SELECT container.name, block.name, adminrole.name
FROM block
    LEFT JOIN container_block ON container_block.blockid=block.id
    LEFT JOIN container ON container.id=container_block.containerid
    LEFT JOIN adminacl ON adminacl.objectid=container.id
    JOIN adminrole ON adminrole.id=adminacl.adminid
WHERE
    adminacl.objecttype='Container'
    AND block.blockstatus !=1 #to exclude unassigned blocks
ORDER BY container.name, block.name;

Answer 1

我喜欢从我真正想要的最大数量开始。从你的问题来看，我认为你想要一个包含容器和管理角色的 block 列表，所以我从那里开始并左连接附加信息来创建列表。

SELECT Block.name, Container.name, adminrole.name from 
Block
    LEFT JOIN container_block ON container_block.containerid = Block.id
    LEFT JOIN Container ON Container.id = container_block.containerid
    LEFT JOIN AdminACL ON AdminACL.objectid = Container.id
    RIGHT JOIN adminrole_admin ON adminrole_admin.adminid #there could be multiple roles
    LEFT JOIN adminrole ON adminrole.id = adminrole_admin.adminroleID
WHERE 
    AdminACL.ObjectType = "Container"
    AND adminrole.name  not like 'hidden_%'; 
# shouldn't you have a `hidden` column on the table, instead of prefixing the name?

免责声明:我凭内存写了这篇文章，甚至没有对任何内容进行测试，但是尝试一下，它可能会让您走上正确的道路。

LEFT JOIN 将排除不符合连接条件的行，因此您不应该得到大量不相关的空白，但我假设可能有很多管理员角色，所以我使用 RIGHT JOIN 来生成行包含 adminrole 和 Block 的所有可能组合。这意味着同一 block 会出现不同的管理员角色，并且相同的管理员角色将出现在多个 block 上。您的应用程序需要适本地处理这种重复。