INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Select duplicate charges made on same card same day

Select duplicate charges made on same card same day

Select duplicate charges made on same card same day

(OP)
I am trying to locate all records paid by the same card (last 4 digits were stored and column name is last_4) on the same date (column is called date_made, and is in the date_time format)

CODE

SELECT *
FROM res_reservations a
INNER JOIN res_reservations b
ON a.last_4=b.last_4
WHERE a.date_made LIKE 'b.date_made%' 

... and sadly I get zero results when there should be thousands..

Please help!

RE: Select duplicate charges made on same card same day

Hi

If I understand you correctly :

CODE --> MySQL

select
last_4,date(date_made),count(*)

from res_reservations

group by last_4,date(date_made)

having count(*)>1; 

Feherke.
http://feherke.github.com/

RE: Select duplicate charges made on same card same day

(OP)
Thanks a lot Feherke.
I guess I have to add some conditions in there, I can't just go by that group.
Many people's last 4 digits of cards match up. So additionally, I detect real last_4 duplicates then either by their last names being the same, or by their phone number being the same or by their ip address being the same. So How do I add these conditions in there?

RE: Select duplicate charges made on same card same day

Hi

Quote (lifelineamerica)

So How do I add these conditions in there?
Just enumerate them in the group by clause.

Feherke.
http://feherke.github.com/

RE: Select duplicate charges made on same card same day

(OP)
Like how? please post an example.
last_4 and date have to be the same.
But then ONLY ONE not all of the others have to be the same.
So if the names and the phone numbers then don't match but the ipaddress does match good enough for me.
Or if the ipaddress and the phone numbers don't match but the last names do match good enough for me

You get the idea.

RE: Select duplicate charges made on same card same day

Hi

Quote (lifelineamerica)

But then ONLY ONE not all of the others have to be the same.
Oops, I misunderstood you. Then group by will not help.

Try this, it seems to work for my sample data :

CODE --> MySQL

select
last_4,date(date_made),count(*)

from res_reservations

group by last_4,date(date_made)

having count(*)>1
and (count(distinct lastname)=1 or count(distinct phone)=1 or count(distinct ipaddress)=1) 

Feherke.
http://feherke.github.com/

RE: Select duplicate charges made on same card same day

(OP)
Thanks that did it. Another star for you!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close