Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
|
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 11th, 2009, 07:10 PM
Registered User
 
Join Date: Nov 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Question 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.
 
Old November 29th, 2009, 02:18 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 373
Thanks: 0
Thanked 1 Time in 1 Post
Default

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!
__________________
- Rakesh
http://iam-rakesh.blogspot.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL said: select command denied to user: 'root@ surendran MySQL 2 September 9th, 2010 12:27 AM
Chapter 7 - Using ADO Command.Execute Ragtime BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 0 December 23rd, 2008 03:55 AM
Populate Select Menu from MySQL Database joviyach PHP Databases 4 June 24th, 2006 09:38 PM
connection.execute and command.execute difference vinod_yadav1919 Classic ASP Databases 0 September 28th, 2005 06:34 AM
conn.execute,command.execute or rs.open vinod_yadav1919 Crystal Reports 0 January 3rd, 2005 10:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.