?
Solved

Match emails and Ips in a query

Posted on 2011-02-26
9
Medium Priority
?
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

719 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