Postgres Pro
Downloads
Home   >   mailing lists

Thread: JOIN question with multiple records

JOIN question with multiple records

From
"Scott, Casey"
Date:
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

Re: JOIN question with multiple records

From
Richard Huxton
Date:
Scott, Casey wrote:
> 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


I think you've got the design of this table wrong.

It looks like you're leaving mac2 NULL where the server has only one 
network-card. This is wrong - mac2 is not "unknown" it is "card not 
present" or similar (and the type of the column should then be not 
mac-address but mac-address-and-not-present).

I'm also not sure how you will handle the case when a server has 3 
network-cards. Also, if you want to know which server has a specific 
mac-addr then you'll need to check two columns with your current design.

If possible I'd suggest reworking the table to something like: (name, 
card-id, mac-addr) and you'd then have:
SERVER2  0  00:0d:56:ba:ad:93
SERVER2  1  00:0d:56:ba:ad:96
...

Then a crosstab function / case statement can reformat your query output 
as required.

> SELECT servers.name,addresses.ipaddr,servers.application_mgr FROM
> servers LEFT JOIN addresses ON addresses.mac = servers.mac OR
> addresses.mac = servers.mac2

Well, if you can't change the structure of your tables you could do 
something like:

SELECT ...
FROM  servers s
LEFT JOIN  addresses a1
ON  s.mac = a1.mac
LEFT JOIN addresses a2
ON s.mac = a2.mac

The crucial bit is aliasing the "addresses" table twice.
--   Richard Huxton  Archonet Ltd