0

I have This table :

    SET NAMES utf8;
SET time_zone = '+00:00';

CREATE TABLE `app_log` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `serial` int(11) NOT NULL,
  `dataEntryIP` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `dataEntryUA` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `domain` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `timeStamp` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `serial` (`serial`)
) ENGINE=InnoDB AUTO_INCREMENT=4328 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Sample of the possible data would be like :

INSERT INTO `app_log` (`ID`, `serial`, `dataEntryIP`, `dataEntryUA`, `domain`, `timeStamp`) VALUES
(6,33605,'2.5.29.18','Google Chrome','www.domain1.com','2017-08-18 18:44:23'),
(7,33606,'8.102.167.213','Google Chrome','domain1.com','2017-08-18 18:45:48'),
(8,33607,'8.24.10.14','Google Chrome','www.domain0.com','2017-08-18 18:58:13'),
(9,33608,'8.24.10.14','Google Chrome','domain0.com','2017-08-18 18:58:13'),
(10,33609,'8.24.10.14','Google Chrome','domain1.com','2017-08-18 19:06:44'),
(11,33610,'8.24.10.14','Google Chrome','www.domain0.com','2017-08-18 19:06:44'),
(12,33611,'7.10.14.11','Google Chrome','domain2.com','2017-08-18 19:12:48'),
(13,33612,'7.10.14.11','Google Chrome','www.domain2.com','2017-08-18 19:12:48'),
(14,33613,'9.45.21.18','Google Chrome','domain3.com','2017-08-18 19:18:10');

Now I am in need of selecting any IP which have used more than one domain, for this sample, it is : 8.24.10.14 because it have used both domains : domain0 and domain1

How would I do this ??

Thank you very much

0

You can use this query:

SELECT dataEntryIP FROM app_log
GROUP BY dataEntryIP
HAVING COUNT(*) > 1

Be careful above query doesn't use any index.

If your table will be grow, index the dataEntryIP column

ALTER TABLE `app_log`
ADD INDEX `dataEntryIP` (`dataEntryIP`);

Update 1

Oh sorry I did not understand your question.

I suggest before you insert the data into app_log remove www. prefix to unify domains (www.domain1.com is equal to domain1.com).

But for now I think it can help:

SELECT dataEntryIP from (
SELECT replace(domain, 'www.', '') as domain_without_www ,dataEntryIP from 
app_log
GROUP BY domain_without_www,dataEntryIP ) t1
GROUP BY dataEntryIP 
HAVING COUNT(*) > 1

But It is very slow, if your table is big.

if you can remove www. before insert to table, your query will be easier and faster.

  • Thank you for help, but I think I have failed to explain that there are many duplicated rows, but not always different with the domain, I want only those who used different domains (like domain1 and domain2 , not like domain1 and www.domain1) Thank you – Fanar Web Sep 9 '17 at 7:41
  • Oh sorry @FanarWeb, I updated my post, look at Update 1 – HamoonDBA Sep 9 '17 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.