About a SELECT command to execute in my MysQL Database

Expand Post »

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.

Similar Threads

Carcinosi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Nov 2009

Permalink

Nov 12th, 2009

Re: About a SELECT command to execute in my MysQL Database

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

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

nav33n

Moderator

Featured Poster

Reputation Points: 524

Solved Threads: 356

Purple hazed!

Offline

3,878 posts
since Nov 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: How to store a big database

Next Thread in MySQL Forum Timeline: How to get the percentage??

DaniWeb Message

Cancel Changes

User Name
Password