Solved

Match emails and Ips in a query

Posted on 2011-02-26
9
280 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:babak62
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 24

Expert Comment

by:jimyX
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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 …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now