I have a list of visitors whose IPs I have recorded. Now for a specific report, I need to see which country each visitor has come from.

I divided this task into two part, first getting all the IPs of the user who have uniquely logged in and the second part is to search two tables with country/IP information (from ip2nation) and to get the country with the provided IP.

First Part - getting all the IPs of the user who have uniquely logged in
------------------------------------------------------------------------

```
SELECT
     a.uid, a.hostname, a.timestamp, 
 COUNT(*) AS times
FROM
  login_activity a
GROUP BY
  a.hostname
ORDER BY
  times desc

```

This gave me IPs (hostname) of all the past-logged in users. Works fine.

Second Part - Get country from the two tables (both have thousands of records) by inputting IP
----------------------------------------------------------------------------------------------

```
SELECT 
    c.country
FROM 
    ip2nationCountries c, ip2nation i
WHERE 
    i.ip < INET_ATON(  "157.191.122.36" ) 
AND 
    c.code = i.country

ORDER BY i.ip DESC 
LIMIT 0 , 1

```

This works great too.

Now, for the real problem. Joining these two queries, to get the country (instead of an IP) from all the logged in users. This is what I wrote:-

```
        SELECT
         a.uid, a.hostname, a.timestamp, c.country, 
         COUNT(*) AS times
        FROM
          login_activity a, ip2nationCountries c, ip2nation i
        WHERE
           i.ip < INET_ATON(a.hostname)     
           AND c.code = i.country

        GROUP BY
          a.hostname
        ORDER BY
          times desc;

```

This has two problems:-

* it takes a lot of time to load.
* it gives wrong data (showing the visits in thousands for each row).
* Basically, it shows all the data wrong.

Could you help me in making this SQL?

Just in case, the structure/data of the tables are below:-

Structure/Data of the Tables is :-
----------------------------------

**ip2nation** (has a lot of data)
---------------------------------

(Structure)

```
CREATE TABLE ip2nation (
  ip int(11) unsigned NOT NULL default '0',
  country char(2) NOT NULL default '',
  KEY ip (ip)
);

```

(Data)

```
INSERT INTO ip2nation (ip, country) VALUES(0, 'us');
INSERT INTO ip2nation (ip, country) VALUES(687865856, 'za');
INSERT INTO ip2nation (ip, country) VALUES(689963008, 'eg');
INSERT INTO ip2nation (ip, country) VALUES(691011584, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691617792, 'zw');
INSERT INTO ip2nation (ip, country) VALUES(691621888, 'lr');
INSERT INTO ip2nation (ip, country) VALUES(691625984, 'ke');
INSERT INTO ip2nation (ip, country) VALUES(691630080, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691631104, 'gh');
INSERT INTO ip2nation (ip, country) VALUES(691632128, 'ng');
INSERT INTO ip2nation (ip, country) VALUES(691633152, 'zw');
INSERT INTO ip2nation (ip, country) VALUES(691634176, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691650560, 'gh');
INSERT INTO ip2nation (ip, country) VALUES(691666944, 'ng');
INSERT INTO ip2nation (ip, country) VALUES(691732480, 'tz');
INSERT INTO ip2nation (ip, country) VALUES(691798016, 'zm');
INSERT INTO ip2nation (ip, country) VALUES(691863552, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691994624, 'zm');
INSERT INTO ip2nation (ip, country) VALUES(692011008, 'za');
INSERT INTO ip2nation (ip, country) VALUES(692027392, 'mg');
INSERT INTO ip2nation (ip, country) VALUES(692035584, 'ao');
INSERT INTO ip2nation (ip, country) VALUES(692043776, 'na');
INSERT INTO ip2nation (ip, country) VALUES(692060160, 'eg');
INSERT INTO ip2nation (ip, country) VALUES(692191232, 'ci');
INSERT INTO ip2nation (ip, country) VALUES(692207616, 'za');
INSERT INTO ip2nation (ip, country) VALUES(692240384, 'gh');
INSERT INTO ip2nation (ip, country) VALUES(692256768, 'sd');

```

**ip2nationCountries** (has a lot of data)
------------------------------------------

(Structure)

```
CREATE TABLE ip2nationCountries (
  code varchar(4) NOT NULL default '',
  iso_code_2 varchar(2) NOT NULL default '',
  iso_code_3 varchar(3) default '',
  iso_country varchar(255) NOT NULL default '',
  country varchar(255) NOT NULL default '',
  lat float NOT NULL default '0',
  lon float NOT NULL default '0',  
  PRIMARY KEY  (code),
  KEY code (code)
);

```

(Data)

```
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ad', 'AN', 'AND', 'Andorra', 'Andorra', 42.3, 1.3);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ae', 'AR', 'ARE', 'United Arab Emirates', 'United Arab Emirates', 24, 54);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('af', 'AF', 'AFG', 'Afghanistan', 'Afghanistan', 33, 65);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ag', 'AT', 'ATG', 'Antigua and Barbuda', 'Antigua and Barbuda', 17.03, -61.48);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ai', 'AI', 'AIA', 'Anguilla', 'Anguilla', 18.15, -63.1);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('al', 'AL', 'ALB', 'Albania', 'Albania', 41, 20);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('am', 'AR', 'ARM', 'Armenia', 'Armenia', 40, 45);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('an', 'AN', 'ANT', 'Netherlands Antilles', 'Netherlands Antilles', 12.15, -68.45);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ao', 'AG', 'AGO', 'Angola', 'Angola', -12.3, 18.3);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('aq', 'AT', 'ATA', 'Antarctica', 'Antarctica', -90, 0);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ar', 'AR', 'ARG', 'Argentina', 'Argentina', -34, -64);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('as', 'AS', 'ASM', 'American Samoa', 'American Samoa', -14.2, -170);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('at', 'AU', 'AUT', 'Austria', 'Austria', 47.2, 13.2);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('au', 'AU', 'AUS', 'Australia', 'Australia', -27, 133);
INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('aw', 'AB', 'ABW', 'Aruba', 'Aruba', 12.3, -69.58);

```

**login\_activity**
-------------------

(Structure)

```
CREATE TABLE IF NOT EXISTS `mslop_login_activity` (
  `aid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier for an activity (session).',
  `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The mslop_users.uid corresponding to a session, or 0 for anonymous user.',
  `host_user_agent` varchar(256) NOT NULL DEFAULT '' COMMENT '$_SERVER["HOST_USER_AGENT"] string. This can be used with get_browser() in PHP.',
  `hostname` varchar(128) NOT NULL DEFAULT '' COMMENT 'The IP address that was used for this session.',
  `timestamp` int(11) NOT NULL DEFAULT '0' COMMENT 'The UNIX timestamp when the session was started.',
  PRIMARY KEY (`aid`),
  KEY `aid` (`aid`),
  KEY `uid` (`uid`),
  KEY `timestamp` (`timestamp`)
);

```

(Data)

```
INSERT INTO `mslop_login_activity` (`aid`, `uid`, `host_user_agent`, `hostname`, `timestamp`) VALUES
(1, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363038356),
(2, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363038374),
(3, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363193841),
(4, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363194789),
(5, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363197889),
(6, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '172.24.1.143', 1363207361),
(7, 35, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363301612),
(8, 35, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363301751),
(9, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363364574),
(10, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363374517),
(11, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363377701),
(12, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363714792),
(13, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363714911),
(14, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363714929),
(15, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363715946),
(16, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_3) AppleWebKit/536.28.10 (KHTML, like Gecko) Version/6.0.3 Safari/536.28.10', '172.24.1.161', 1363791080),
(17, 4, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_3) AppleWebKit/536.28.10 (KHTML, like Gecko) Version/6.0.3 Safari/536.28.10', '172.24.1.161', 1363791124),
(18, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_3) AppleWebKit/536.28.10 (KHTML, like Gecko) Version/6.0.3 Safari/536.28.10', '172.24.1.161', 1363791144),
(19, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/537.22 (KHTML, like Gecko) Chrome/25.0.1364.152 Safari/537.22', '172.24.1.143', 1363791365),
(20, 64, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/537.22 (KHTML, like Gecko) Chrome/25.0.1364.152 Safari/537.22', '172.24.1.143', 1363791650);

```

---

I changed the query as follows, but it still is showing me wrong results ...

Could you guys have a look:-

```
SELECT l.uid,
    l.hostname,
    l.timestamp,
    c.country,
    l.times
FROM ip2nationCountries c
JOIN ip2nation i ON c.code = i.country
JOIN ( SELECT
        a.uid,
        a.hostname,
        MAX(a.timestamp) AS timestamp,
        COUNT(*) AS times
    FROM mslop_login_activity a
    WHERE a.uid = 3
    AND a.hostname = "157.191.122.36"
    GROUP BY a.hostname) AS l ON i.ip < INET_ATON( l.hostname )

```