hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bennie Leo <tben...@hotmail.com>
Subject Limiting outer join
Date Mon, 06 Jul 2015 23:31:01 GMT
 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
 

 		 	   		  
Mime
View raw message