How to display multiple tables from one ID column using MySQL query

Question

I'm trying to find this query where I want to show which hosts use which template in my Zabbix table. The only problem is that the hosts and templates are registered in the same table. They are mixed in the table, for example ID 11813 is the host and 11815 is the template. Now I found a table that defines the relationship between these two: hosts_templates.

The table has 3 columns: host_template id, hostid, templateid

The hosts table has many columns, but also contains: hostid, name, where hostid contains host and template. The table host does have a templateid column, but it is not used.

In the table hosts_templates, I can see which hosts use which template. The only problem is that I see the ID and want to see the names that match that ID. What I have so far:

Output of table hosts_templates

Output from name, host ID 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 of these queries shows half of my solution, but with duplications.

The problem is that I can't select a different name for the second column, so it just repeats the first column, which is not what I want... and since I've already joined the host ID internally, I can't do that for the second column Second-rate. So I need a combination of the above 2 sql queries. I feel like I'm close, but I just can't get it.

Any help would be greatly appreciated!

Answer 1

This is a basic question. You should know more about SQL syntax such as chain joins, accessing the same column name from different tables.

Sample code:

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

Answer 2

You must join twice. Give the tables different aliases so that 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