JOIN question with multiple records
От | Scott, Casey |
---|---|
Тема | JOIN question with multiple records |
Дата | |
Msg-id | 9A072150141F5C489DC92CFCB8C0FD4905E296D5@e2ksea2.wotc.wizards.com обсуждение исходный текст |
Ответы |
Re: JOIN question with multiple records
|
Список | 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
В списке pgsql-sql по дате отправления: