Hi,
In the following query, it is possible to limit the amount of entries returned
by an outer join to a single value? I want to obtain a single country from
ipv4geotable for each entry in logontable.
CREATE TABLE ipv4table AS
SELECT logon.IP, ipv4.Country
FROM
(SELECT * FROM logontable WHERE isIpv4(IP)) logon
LEFT OUTER JOIN
(SELECT StartIp, EndIp, Country FROM ipv4geotable) ipv4 ON isIpv4(logon.IP)
WHERE ipv4.StartIp <= logon.IP AND logon.IP <= ipv4.EndIp;
For instance, if I had the IP "W.X.Y.Z" in logontable, and that "W.X.Y.Z" fell
in the range of both Italy and Spain in ipv4geotable, then I would like to
associate it with Italy only.
I've tried adding "LIMIT 1" to the second subquery :(SELECT StartIp, EndIp,
Country FROM ipv4geotable LIMIT 1) ipv4 ON isIpv4(logon.IP)but this is wrong
since the WHERE clause has to traverse all IPs. Limiting the where clause
doesn't help either.
Any ideas?
Thank you!
B
