MySQL

About a SELECT command to execute in my MysQL Database

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2009

Posts: 1

Reputation: Carcinosi is an unknown quantity at this point

Solved Threads: 0

Carcinosi

Offline

Newbie Poster

About a SELECT command to execute in my MysQL Database

19 Days Ago

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.

•

Join Date: Nov 2007

Posts: 3,748

Reputation: nav33n is a jewel in the rough

Solved Threads: 331

nav33n

Offline

Senior Poster

18 Days Ago

Hi, If I understand your question right, this should do the trick.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT * FROM customers c, cust_packages cp 
WHERE c.cust_custnr = cp.cpack_usernr GROUP BY cpack_usernr
HAVING count( * ) =1 AND cpack_canceldate != '0000-00-00'
select * from customers c, cust_packages cp 
where c.cust_custnr = cp.cpack_usernr group by cpack_usernr
having count( * ) =1 and cpack_canceldate != '0000-00-00'

This will select all the customers with only 1 record in table cust_packages (meaning, they have not registered again) and their canceldate can be anything but not 0000-00-00.
Cheers!
Nav

Ignorance is definitely not bliss!

*PM asking for help will be ignored*