mysql 查询显示来自一个 ID 列的多个表 [英] mysql query show multiple tables from one ID column

问题描述

我试图找到这个查询，我想在其中显示哪些主机使用我的 Zabbix 表中的哪个模板.唯一的问题是主机和模板注册在同一个表中.它们混合在表中，例如 ID 11813 是主机，11815 是模板.现在我找到了一个表，其中定义了这两个之间的关系:hosts_templates.

I'm trying to find this query where I want to show which hosts uses which template from my Zabbix table. The only problem is that hosts and templates are registered in the same table. They are mixed in the table with for example ID 11813 being a host and 11815 being a template. Now I've found a table where the relation between these 2 is defined: hosts_templates.

此表有 3 列:一个host_template id、hostid、templateid

This table has 3 columns: a host_template id, hostid, templateid

hosts 表有很多列，但也包含:hostid、name 其中 hostid 包含主机和模板.表 hosts 确实有一个 templateid 列，但它没有被使用.

The table hosts has many columns but also containing: hostid, name where hostid contains the hosts as well as the templates. the table hosts does have a templateid column but IT IS NOT USED.

在表 hosts_templates 我可以看到哪些主机使用哪个模板.唯一的问题是我看到了 ID，我想看到与该 ID 匹配的名称.到目前为止我所拥有的:

In the table hosts_templates I can see which hosts uses which template. The only problem is I see the IDs and I want to see the name matching that ID. What I have so far:

来自表 hosts_templates 的输出

output from table hosts_templates

来自名称的输出，来自表hosts的hostid

output from name, hostid from table hosts

到目前为止我尝试过的:

what I have tried so far:

select name, name
  from hosts_templates
 inner join hosts on hosts_templates.hostid = hosts.hostid;

select name, name
  from hosts_templates
 inner join hosts on hosts_templates.templateid = hosts.hostid;

这些查询的输出显示了我的解决方案的一半，但重复了.

The output from these queries shows half of my solution, but duplicated.

问题是我不能为第二列选择一个不同的名称，所以它只是复制了第一列，这不是我想要的......而且因为我已经加入了内部主机 ID，所以我不能这样做第二次.所以我需要像上面 2 个 sql 查询的组合.我有一种离我很近的感觉，但我就是无法理解.

the problem is I can't pick a different name for the second column so it just duplicates the first column which is not what I want... And as I already have inner joined the hostid i can't do it a second time. So I need like a combination of the 2 sql queries above. I have the feeling i'm so close but I just can't get it.

任何帮助将不胜感激！

推荐答案

您必须加入两次.为表格指定不同的别名，以便您区分它们.

You have to join twice. Give the table different aliases so you can distinguish them.

SELECT h1.name as host_name, h2.name AS template_name
FROM hosts_template AS t
JOIN hosts AS h1 ON t.hostid = h1.hostid
JOIN hosts AS h2 ON t.hosttemplateid = h2.hostid

这篇关于mysql 查询显示来自一个 ID 列的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！