p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   MySQL (http://p2p.wrox.com/forumdisplay.php?f=99)
-   -   About a SELECT command to execute in my MysQL Database (http://p2p.wrox.com/showthread.php?t=76997)

Carcinosi November 11th, 2009 07:10 PM

About a SELECT command to execute in my MysQL Database
 
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.

iamrakesh22 November 29th, 2009 02:18 AM

You can try by joining 'customers' table with a temporary table instead of directly joining 'cust_packages'.
you query might look
'SELECT ... FROM customers AS c, (SELECT .. FROM cust_packages WHERE cpack_canceldate IS NOT NULL) cc
WHERE c.cust_custnr=cc.cpack_usernr'

What do you think!


All times are GMT -4. The time now is 01:13 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.