Re: special join

In article <[email protected]>,
Jan Novak  <[email protected]> wrote:
> Am 07.09.20 um 14:19 schrieb Jan Novak:
> > Am 07.09.20 um 14:15 schrieb The Natural Philosopher:
> >> On 07/09/2020 11:26, Jan Novak wrote:
> >>> Hi,
> >>>
> >>> i have a table "server" with auto_increment field ID and a table 
> >>> "ip", with a filed "SERVER_ID" corrosponding to "server.ID"
> >>>
> >>> select ip.VALUE as "ip" from server, ip where server.DELETED=0 and 
> >>> server.ID=ip.SERVER_ID
> >>>
> >>> If in "ip" table for the server are more then one ip adress are 
> >>> saved, i get in my results the server also more then one time listed.
> >>>
> >>> 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.
> >>>
> >>> Jan
> >>
> >> order by....unique
> > 
> > sorry, i dont understand.
> > What should be orderd abd unique set?
> 
> (Sorry for my bad english)
> 
> 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.

Cheers
Tony
-- 
Tony Mountifield
Work: [email protected] - http://www.softins.co.uk
Play: [email protected] - http://tony.mountifield.org