Re: special join

On 07/09/2020 12.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.

What do you mean as first?

  - Lowest ip-number (string or value vise)?
  - Highest ip-number (string or value vise)?
  - First line inserted for the server (lowest id or oldest timestamp)?

For the two first you use a group by with max/min as Tony showed, if you 
are thinking of the numeric value and not the string value of the ip, 
then you need to cast the value with INET_ATON() (convert it back with 
INET_NTOA()).

If you want the first value used for the server, then you will need to 
have a way of distinguish which row is inserted first, either by a row 
id or a timestamp, in this case you would have to do something like:

SELECT s.ID, p.VALUE AS port, i.VALUE AS ip

FROM server
  s
INNER JOIN (SELECT MIN(p2.PORT_ID) PORT_ID, p2.SERVER_ID FROM port p2 
WHERE p2.SERVER_ID = s.SERVER_ID GROUP BY p2.SERVER_ID) AS port2 ON 
port2.SERVER_ID = s.SERVER_ID
INNER JOIN (SELECT MIN(i2.IP_ID) IP_ID, p2.SERVER_ID FROM ip i2 WHERE 
i2.SERVER_ID = s.SERVER_ID GROUP BY p2.SERVER_ID) AS ip2 ON 
ip2.SERVER_ID = s.SERVER_ID
INNER JOIN ip i ON i.SERVER_ID = server.ID
  and i.IP_ID = ip2.IP_ID
INNER JOIN port p ON p.SERVER_ID = server.ID
  and p.PORT_ID = port2.PORT_ID
WHERE server.DELETED = 0


It does have two sub selects, which can slow things down a lot if you 
don't have indexes.

-- 

  //Aho