Re: special join

Am 07.09.20 um 17:33 schrieb Tony Mountifield:
>>>>> My question is: how can i get only the server with the first IP from
>>>>> the ip Table, if more then one ip's saved there.
>> The complete sql string is like that (with additional Infos from "port"
>> table):
>>
>> select port.VALUE as "port", ip.VALUE as "ip" from server, port, ip
>> where server.DELETED=0 and server.ID=port.SERVER_ID and
>> server.ID=ip.SERVER_ID
>>
>> In ip Table are for a host 4 rows, but i like to have for the host only
>> the first one.
> 
> As Kees said in his reply to you, you need to define what you mean by "first".
> 
> Here is one possibility, rewritten to use explicit joins instead of implicit joins:
> 
> SELECT server.ID, MIN(port.VALUE) AS "port", MIN(ip.VALUE) AS "ip"
> FROM server
> INNER JOIN ip ON ip.SERVER_ID = server.ID
> INNER JOIN port ON port.SERVER_ID = server.ID
> WHERE server.DELETED = 0
> GROUP BY server.ID
> 
> But MIN() is only one possible way to select a single IP or port out of those available.
> You need to decide how you want to choose.

Without the MIN Statement it works like expected. Many thanks for your help.

Jan