I have 2 tables. One containing information about servers, and the other
containing information about IP addresses.
E.G.
Server table:
name mac
mac2
-------------------------------------------------------------
SERVER1 00:0d:56:ba:ad:92
SERVER2 00:0d:56:ba:ad:93
00:0d:56:ba:ad:96
SERVER3 00:0d:56:ba:ad:94
SERVER4 00:0d:56:ba:ad:95
00:0d:56:ba:ad:97
Address table:
ip mac
--------------------------------
10.0.0.1 00:0d:56:ba:ad:92
10.0.0.2 00:0d:56:ba:ad:92
10.0.0.3 00:0d:56:ba:ad:94
10.0.0.4 00:0d:56:ba:ad:95
I need a query that will return all the IP addresses that match a
server's mac address along with the rest of the information about the
server in this format:
name ip mac
mac2
-----------------------------------------------------------------------
SERVER1 10.0.0.1 00:0d:56:ba:ad:92 10.0.0.2
SERVER2 00:0d:56:ba:ad:93
00:0d:56:ba:ad:96
SERVER3 10.0.0.3 00:0d:56:ba:ad:94
SERVER4 10.0.0.4 00:0d:56:ba:ad:95
However, the best that I've done is to return a record of the server for
every IP that matches in the address table:
name ip mac
mac2
-----------------------------------------------------------------------
SERVER1 10.0.0.1 00:0d:56:ba:ad:92
SERVER1 10.0.0.2 00:0d:56:ba:ad:92
SERVER2 00:0d:56:ba:ad:93
00:0d:56:ba:ad:96
SERVER3 10.0.0.3 00:0d:56:ba:ad:94
SERVER4 10.0.0.4 00:0d:56:ba:ad:95
An abbreviate version of query looks like this. Ultimately there will be
a WHERE condition on the query:
SELECT servers.name,addresses.ipaddr,servers.application_mgr FROM
servers LEFT JOIN addresses ON addresses.mac = servers.mac OR
addresses.mac = servers.mac2
Does anyone have any suggestions on this?
Regards,
Casey