From: Richard Huxton <dev@archonet.com>
To: Scott, Casey <Casey.Scott@wizards.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: JOIN question with multiple records
Date: Thu, 05 Jan 2006 08:40:56 +0000
Message-ID: <43BCDB98.7030005@archonet.com> (raw)
In-Reply-To: <9A072150141F5C489DC92CFCB8C0FD4905E296D5@e2ksea2.wotc.wizards.com>
References: <9A072150141F5C489DC92CFCB8C0FD4905E296D5@e2ksea2.wotc.wizards.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, Casey.Scott@wizards.com
Subject: Re: JOIN question with multiple records
In-Reply-To: <43BCDB98.7030005@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox