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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
