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

查看:122
本文介绍了mysql查询从一个ID列显示多个表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正试图在此查询中查找我想显示哪些主机使用我的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

表host具有许多列,但也包含:hostid,其中hostid包含主机以及模板的名称.表主机确实具有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

名称输出,表主机的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.

问题是我无法为第二列选择其他名称,因此它只是复制了第一列,这不是我想要的...因为我已经加入了hostid,所以我无法做到这一点.第二次.因此,我需要上面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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆