Solved

Match emails and Ips in a query

Posted on 2011-02-26
9
288 Views
Last Modified: 2012-05-11
Hi;
I am trying to find users with the same emaul and IP also finding duplicate accounts with a query and already wrote this but I do not know if this works correctly

SELECT
t_user.USERID,
t_user.EMAILID,
t_user.DOB,
t_user.IP_REGISTER,
t_user.PLAY_CHIPS
FROM
t_user
WHERE EMAILID = EMAILID and IP_REGISTER = IP_REGISTER
ORDER BY EMAILID, PLAY_CHIPS
0
Comment
Question by:babak62
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 34986310
You can try something like this:

SELECT t_user.USERID,t_user.EMAILID,t_user.DOB,t_user.IP_REGISTER,t_user.PLAY_CHIPS FROM t_user GROUP BY EMAILID, IP_REGISTER HAVING (COUNT(EMAILID) > 1), (COUNT(IP_REGISTER) > 1) ORDER BY EMAILID, PLAY_CHIPS
0
 

Author Comment

by:babak62
ID: 34986320
thanks a lot   but this is what I got

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' (COUNT(IP_REGISTER) > 1) ORDER BY EMAILID, PLAY_CHIPS' at line 2
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34986337
Updated:

SELECT t_user.USERID,t_user.EMAILID,t_user.DOB,t_user.IP_REGISTER,t_user.PLAY_CHIPS FROM t_user GROUP BY EMAILID, IP_REGISTER HAVING (COUNT(EMAILID) > 1) and (COUNT(IP_REGISTER) > 1) ORDER BY EMAILID, PLAY_CHIPS
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:babak62
ID: 34986357
Thanks for the answer and please look at the sample record I added here.  Some of the records follos the query but in the middle you see results like this and that I can not explain also IP are totally differnt so are the emails.  Is there anyway to eleminate these records and get exact matches?
bebemon	IFIcFlYeWDsZBlRAXClYBwFzC1UMAgEfUVwr	1969-12-31 19:00:00	121.54.29.102	10000
khakie	IFIdDFodWhQIHFdXUDJaAQpzC1UMAgEfUVwr	1969-12-31 19:00:00	112.204.100.235	2320
fastrack_07	IFIdEEETVw8yXgZySidbAQsdEVsJ	1969-12-31 19:00:00	203.87.192.206	165
luis3	IFIdEFIcUBAFC1dHQS9cGxdsAFUBLRdQWlwpHQ0LXg==	1969-12-31 19:00:00	112.204.248.8	6091
MarLahPendergat	IFIDEV8TRgcMAF5tUjZBBwhBHUcBLRdQWlwpHQ0LXg==	1969-12-31 19:00:00	202.8.232.144	1820
nicco	IFIEBUEWWwwCGlVdVBkDViRKE1wLAkBSXV5oQwY=	1969-12-31 19:00:00	119.94.97.125	3765
www.putagalpu.com	IFIfEVocWyQUA1BbX2hQAQk=	1969-12-31 19:00:00	112.198.78.122	6320
fuuutaaagalpuuu	IFIfEVocWyQUA1BbX2hQAQk=	1969-12-31 19:00:00	112.198.78.80	6480
flwn	IFIfO0QbWjtdX3FLUi5cAUpQHVk=	1969-12-31 19:00:00	151.16.54.170	9430

Open in new window

0
 
LVL 24

Expert Comment

by:jimyX
ID: 34986679
Let me tell you what results this query returns may be your expectation is different but the query returns what it meant to do.
The above query will return only the records that are having same email & IP more than one time.

e.g:
USERID EMAILID DOB IP_REGISTER PLAY_CHIPS
    a          b           c           d                    e
     a          n           c           d                    e
    a          o           c           d                    e
     a          b           c           k                    e
    a          b           c           d                    e
    a          o           c           d                    e

so the result will be:

USERID EMAILID DOB IP_REGISTER PLAY_CHIPS
    a          b           c           d                    e
    a          o           c           d                    e

Is this what you are after because the result you got up there could be correct.
0
 

Author Comment

by:babak62
ID: 34987658
when I see the results there should not be a single record there also if the email is the same and the ip is the same, also a duplicate account,  but in this results I see records which are unique and my question is what are they doing there.
The goal with the query is to find players with more than one account created and in this case the query when it finds the player should show both accounts then a single record does not mean anything in the resullts.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34989249
If what I mentioned in my post (34986679) is what you want then the query works fine as I tested it with some data and it worked. Probably there is something else. Can you test the query in a smaller DB for easy tracking or can you try with another sample?
0
 

Author Comment

by:babak62
ID: 34989538
This is an another sample and as you see in the middle of it there is one record which is irrelavant to the rest and this is what I am talking about.
akin_na_yan_1018	dwFdJEoTXAsCQFJdXg==	122.53.175.50	
sea330	          dwFdJEoTXAsCQFJdXg==	63.95.64.254	
holiday1489	dwFdJFsdQAkMB10cUCle	63.95.64.254
SEE_24	          dwFdUAYybSUlIX4ccAl+	110.93.80.137
CobraMan	          dwFdUAYyTQUFAV4cUCle	112.203.22.112
tarantado-ako	dwFdUAZEdB0MBl5dHSVcAw==	112.203.17.74
LordOfGamblers	dwFdUAZEdB0MBl5dHSVcAw==	124.104.131.133
pinsan	          dwFdUAZEdB0MBl5dHSVcAw==	126.203.179.101
maw-tay	          dwFdUAZEdB0MBl5dHSVcAw==	126.251.138.184
eowfuh	          dwFdUAZEdB0MBl5dHSVcAw==	210.4.121.218

Open in new window

0
 
LVL 24

Accepted Solution

by:
jimyX earned 500 total points
ID: 35008815
Sorry to be late. I am afraid I am not seeing what you are trying to say, can you show me your data (the sample) and the result you expect from the query?
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question