June 21, 2013 at 3:26 pm
Hi,
I am badly stuck in making the query which I guess needs a couple of joins.
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, I am putting in Structure and Sample Data of all three Tables.
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);
Thanks.
June 21, 2013 at 3:44 pm
There are a few issues here and I will try to help you as much as I can, but I'm not sure that I can give you a final solution.
First of all, your code is from MySQL and this is a SQL Server forum, there are differences as not having an INET_ATON function, limit N and the way to handle strings.
However, the join might look like this:
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 login_activity a
GROUP BY a.hostname) AS l ON i.ip < INET_ATON( l.hostname )
June 21, 2013 at 4:03 pm
Thank you very much Luis,
The query does work but it shows wrong results for the IP.
For example, it shows me wrong country for the provided IP.
Lets say if I say the UID of the user is 3 and I am searching for the IP of "157.191.122.36", I wrote the following Query.
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
GROUP BY a.hostname) AS l ON i.ip < INET_ATON( "157.191.122.36" )
It gave me the following result, which is kinda wrong because the IP I entered is for US. Also I am sure most of the data is for US (Very few would be for Australia, but that's it). But the site is showing us different results for the IP that is for US only. Moreover we just need Country and Times the site was visited from it. (Just two fields):-
UID | Hostname | timestamp | Country | Times
-----------------------------------------------------------------------------------
3| 157.191.122.36 | 1371790767 | United States | 20
3| 172.24.1.143 | 1365536569 | United States | 18
3| 172.24.1.161 | 1367537323 | United States | 10
3172.24.1.1661367950054United States8
3157.191.122.361371790767Australia 20
3172.24.1.1431365536569Australia 18
3172.24.1.1611367537323Australia 10
3172.24.1.1661367950054Australia 8
3157.191.122.361371790767China 20
3172.24.1.1431365536569China 18
3172.24.1.1611367537323China 10
3172.24.1.1661367950054China 8
3157.191.122.361371790767Australia 20
3172.24.1.1431365536569Australia 18
3172.24.1.1611367537323Australia 10
3172.24.1.1661367950054Australia 8
3157.191.122.361371790767China 20
3172.24.1.1431365536569China 18
3172.24.1.1611367537323China 10
3172.24.1.1661367950054China 8
3157.191.122.361371790767Japan 20
3172.24.1.1431365536569Japan 18
3172.24.1.1611367537323Japan 10
3172.24.1.1661367950054Japan 8
3157.191.122.361371790767China 20
3172.24.1.1431365536569China 18
3172.24.1.1611367537323China 10
3172.24.1.1661367950054China 8
3157.191.122.361371790767Japan 20
3172.24.1.1431365536569Japan 18
I'll be very thankful for your help.
Thanks.
June 21, 2013 at 4:25 pm
That's because you're making a CROSS JOIN instead of an INNER JOIN.
To give te correct output for a single IP address you need to chage the query.
At least, that's what I understand from the code.
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 )
Edited last line to correct error.
June 21, 2013 at 4:29 pm
Thank you very much Luis.
There is a slight problem, the query gives
#1054 - Unknown column 'a.hostname' in 'on clause'
Do you have an idea, what could be the problem?
Should I write l.hostname there?
Thanks.
June 21, 2013 at 4:31 pm
An error I made when copying and pasting on the last line.
Change it like this
GROUP BY a.hostname) AS l ON i.ip < INET_ATON( l.hostname )
June 21, 2013 at 4:41 pm
Hi Luis,
Sorry it showed wrong results.
All I want is to see total visits for each country; how many visits have been made per country.
Country | Visits
------------------
United States | 291
Korea | 22
China | 12
...
...
The IP should come dynamically come from login_activity table, which provides the IP and the other two tables match it with the country. At the end it is shown in an aggregate manner as shown above.
Right now, the query shows (24,000 rows) as below; it shows all the countries ... although yes, we need to see the results for (User ID)UID=3 but the IP should come from login_table; at this moment, as you can see, it shows the times number for all rows as 20. What we need is the total visits per country.
uidhostnametimestampcountrytimes
3157.191.122.361371790767United States20
3157.191.122.361371790767Australia20
3157.191.122.361371790767China20
3157.191.122.361371790767Australia20
3157.191.122.361371790767China20
3157.191.122.361371790767Japan20
3157.191.122.361371790767China20
3157.191.122.361371790767Japan20
3157.191.122.361371790767Thailand20
3157.191.122.361371790767China20
3157.191.122.361371790767Australia20
3157.191.122.361371790767China20
3157.191.122.361371790767Japan20
3157.191.122.361371790767Thailand20
3157.191.122.361371790767China20
3157.191.122.361371790767Australia20
3157.191.122.361371790767China20
3157.191.122.361371790767Thailand20
3157.191.122.361371790767China20
3157.191.122.361371790767Australia20
3157.191.122.361371790767China20
3157.191.122.361371790767Thailand20
3157.191.122.361371790767Japan20
3157.191.122.361371790767India20
3157.191.122.361371790767China20
3157.191.122.361371790767Malaysia20
3157.191.122.361371790767China20
3157.191.122.361371790767Australia20
3157.191.122.361371790767China20
3157.191.122.361371790767Thailand20
Do you think you could help?
I will be more than grateful to you.
June 23, 2013 at 7:50 am
Hi Luis,
I changed the country tables for better Indexing and wrote the completely new posts and what I tried here :-
http://www.sqlservercentral.com/Forums/Topic1466508-391-1.aspx
I will be very very very very thankful if you can have a look ....
Thanks a lot.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply