From: | "Scott, Casey" <Casey(dot)Scott(at)wizards(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | JOIN question with multiple records |
Date: | 2006-01-04 19:54:50 |
Message-ID: | 9A072150141F5C489DC92CFCB8C0FD4905E296D5@e2ksea2.wotc.wizards.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2006-01-05 01:49:52 | FOREIGN KEYs ... I think ... |
Previous Message | Michael Fuhr | 2006-01-04 17:00:31 | Re: Regular Expression Matching problem... |