Need to run a MySQL join on 2 Zabbix tables to output IP and NAME based on HOSTID

Question

I'm having trouble creating the output from two Zabbix tables. I would like to output IP(interafce) and NAME(hosts) using HOSTID.

interface table

| hostid |       ip     |
|   1    |    1.1.1.1   |
|   2    |    8.8.8.8   |

hosts table

| hostid |        name   
|   1    |    test.server.1  |
|   2    |    test.server.2  |

So i would like something like this

| hostid |      ip       |      name
|   1    |    1.1.1.1    |  test.server.1  |
|   2    |    8.8.8.8    |  test.server.2  |

Thanks for you help

Answer 1

Use a left join:

SELECT
    i.hostid,
    i.ip,
    COALESCE(h.name, 'NA') AS name
FROM interface i
LEFT JOIN hosts h
    ON h.hostid = i.hostid
ORDER BY
    i.hostid;