Search code examples
mysqljoinmany-to-many

mysql query show multiple tables from one ID column

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.

This table has 3 columns: a host_template id, 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.

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:

output from table hosts_templates
output from table hosts_templates

output from name, hostid from table hosts
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.

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.

Any help would be greatly appreciated!

Solution

  • 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