Re: special join

Am 07.09.20 um 19:42 schrieb J.O. Aho:
> 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)?
> 
> 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.


Your suggestion is working also in different ways (i.e. lowest/highest IP).

Thanks a lot.

Jan