[Carcinosi]

Hello,

I have a MySQL 5.0 Database used for management of my service, with 13 tables.

The two tables most important now are "customers" and "cust_packages".

The table "customers" has one column "cust_email". This column has e-mails of customers of the my service.

The table named "cust_packages" has one column "cpack_canceldate". This column has the date of canceled accounts, for example '2008-04-03'. If the account is not canceled, so this column has '0000-00-00'.

Both tables "customers" and "cust_packages" have "cust_custnr" and "cpack_usernr" respectively with interconnected values.

These columns have an identification number exclusive for each customer.

For example, I have one customer with the number '110002' in the column "cust_custnr" of the table "customers". The same number is in the column "cpack_usernr" of the table "cust_packages".

I want to select all e-mails of customers with canceled accounts.

But there is a problem. The same customer may have two accounts...the same number may appear two or more times in the colum "cpack_usernr" of the table "cust_packages". For example, the customer '110002' in the past cancelled his account, but recently he opened another account. Because this, the same number 110002 appear two times in the column "cpack_usernr" of the table "cust_packages". I need only exclusive customers with canceled accounts.

So, I want to select all e-mails of customers with canceled accounts AND ONLY for customers which appear ONE TIME in the colum "cpack_usernr" of the table "cust_packages".

Thanks.

[Martyr2]

Well the ideal situation would have been that if a customer had canceled his account that when it was reinstated that they received a new customer account number. That way you could also track a past account activity in addition to the new account activity. Now having said that, all you need to do is link the customer table to the packages table through the customer id where the date is not 0000-00-00, This will get you all expired packages for all customers. Then use the DISTINCT clause on the customer number. Since you are using the same account number with the email back in the customer table, this is enough to give you a unique list of emails for all customers which have expired accounts.

This post has been edited by Martyr2: 11 November 2009 - 04:12 PM

[rgfirefly24]

Also if your looking to get the customers who have a canceled account and have not resigned up you can either in the where clause use a

where 
cust_custnr NOT IN (SELECT * FROM cust_packages where cpack_canceldate = '0000-00-00')