pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
JOIN question with multiple records 2+ messages / 2 participants [nested] [flat]
* JOIN question with multiple records @ 2006-01-04 19:54 Scott, Casey <Casey.Scott@wizards.com> 2006-01-05 08:40 ` Re: JOIN question with multiple records Richard Huxton <dev@archonet.com> 0 siblings, 1 reply; 2+ messages in thread From: Scott, Casey @ 2006-01-04 19:54 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: JOIN question with multiple records 2006-01-04 19:54 JOIN question with multiple records Scott, Casey <Casey.Scott@wizards.com> @ 2006-01-05 08:40 ` Richard Huxton <dev@archonet.com> 0 siblings, 0 replies; 2+ messages in thread From: Richard Huxton @ 2006-01-05 08:40 UTC (permalink / raw) To: Scott, Casey <Casey.Scott@wizards.com>; +Cc: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-01-05 08:40 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2006-01-04 19:54 JOIN question with multiple records Scott, Casey <Casey.Scott@wizards.com> 2006-01-05 08:40 ` Richard Huxton <dev@archonet.com>
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