Skip site navigation (1) Skip section navigation (2)

JOIN question with multiple records

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 (view raw or flat)
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




Responses

pgsql-sql by date

Next:From: Marc G. FournierDate: 2006-01-05 01:49:52
Subject: FOREIGN KEYs ... I think ...
Previous:From: Michael FuhrDate: 2006-01-04 17:00:31
Subject: Re: Regular Expression Matching problem...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group