Two tables, one of customer contact info, one of credit card info (last 4 digits, expiration, associated customer record). More than one credit card can be associated with a customer_id.

We've got a script which figures out which customers' cards are going to expire next month, and notifies both them and us. Then the customer can log into his control panel and enter the new card.

The problem with the script is that it mails the customer even if they have both an expired card and a good card associated with their customer_id.

So what I need is the SQL query to generate a list of customer email addresses and names and last four digits associated with all customer records that have all expiring cards. Make sense?

Been struggling with this for a couple days now. Thanks for any help.

I have a question for you, how are you storing the expiration date? Is it a string or is it a TIMEDATE or are you storing the expiration date as 2 integers?

I think that I have most of a query written, but could use that information.

Chris Spangler
spanglec@cs.washington.edu

EDIT:
I just reread your post and realized that your date stuff is probably working. I will post a query that I think should work. The only incomplete parts that should need to be filled in are the date things.

Here it is:

SELECT customer.name, customer.email
FROM customer_contact_info customer, credit_card_info cc
WHERE customer.id = cc.id
AND expiration date is invalid
AND customer.id NOT IN (
SELECT customer.id
FROM customer_contact_info cust, credit_card_info cc1
WHERE cust.id = cc1.id
AND expiration date is valid)


It should use the sub-query to find the set of all users that have a valid card on file. I assumed that you are linking the 2 tables by some sort of customer id, so that should be replaced as needed. From the sounds of your post your date checking is working, so I did not try to duplicate it (especially since I'm not sure how you are storing dates).

I hope this helps. Let me know if it needs any work.

Chris Spangler
spanglec@cs.washington.edu

Thanks for your reply.

In the credit card table, expiration data is stored as two integers, month and year. Yes, the two tables have a customer_id that we join the tables on.

We're using MySQL so we don't have subselects available. We played for some time with a clause like MAX(exp_year * 100 + exp_month), but then then we tried to get at the card number, it'd be the number of the other (older) card...

This may be impossible in a single query under MySQL, we're not sure.

Hmm. Maybe what you could do is do it in 2 or 3 queries. The first query would select all of the customers with valid credit cards and create a table to store them in. The second query would select all of the customers with invalid credit cards and store them in a table. Finally, the third query could compare both new tables and return the customers who have expiring credit cards. That is just about the only way I can think of doing it without a sub-query.

I'll keep thinking about it and see if I can come up with another idea, but I hope this helps,

Chris Spangler
spanglec@cs.washington.edu

Ok. I've thought some more on it. I think that this can be done in one query. What it will require is using the group by and having clauses of select queries. I think it will look something like this:

SELECT customer.id, customer.email, MAX(cc.exp_year * 100 + exp_month)
FROM customer_contact_info customer, credit_card_info cc
WHERE customer.id = cc.id
GROUP BY customer.id
HAVING MAX(cc.exp_year * 100 + cc.exp_month) - (current_year * 100 + current_month) <= 1

What this should do is grab the credit cards for each user and filter them so only the one with the latest expiration date is chosen. Then the HAVING clause should filter out all of the cards that expire in more than 1 month.

The other solution to the problem that I can see is to only keep one credit card number on file for each customer. When they update their card number, either remove the previous record and add the new one or use an UPDATE query to change the existing record. This would simplify the query in your script because you would not have to worry about a customer having 1 good card and 1 card that is expiring soon.

Let me know if this works. Good luck!

Chris Spangler
spanglec@cs.washington.edu

Wow I *think* that did the trick!


SELECT C.customer_id, C.email, CC.number,
MAX(CC.exp_year * 100 + CC.exp_month) as expdate
FROM customers C, ccards CC
WHERE C.customer_id = CC.customer_id
AND C.closed = '0000-00-00 00:00:00'
AND C.email != ''
GROUP BY customer_id
HAVING MAX(CC.exp_year * 100 + CC.exp_month) -
(year(now()) * 100 + month(now())) < 2;



I've been experimenting with adding additional cards, both expired and not, and everything seems to be working.

Thank you!

No problem. Figuring it out was good practice for me. I'm taking a course on databases right now and this was similar to what could show up on a homework or an exam. I'm glad I was able to help you figure it out.

Chris Spangler
spanglec@cs.washington.edu

What was homework practice to you was a business solution for us. Keep us in mind when you graduate.... we're just up the road in Missoula, Montana.