Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
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.
Reply With Quote
  #2 (permalink)  
Old November 29th, 2009, 02:18 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hyderabad, A.P., India.
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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL said: select command denied to user: 'root@ surendran MySQL 2 September 9th, 2010 01: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 10:38 PM
connection.execute and command.execute difference vinod_yadav1919 Classic ASP Databases 0 September 28th, 2005 07:34 AM
conn.execute,command.execute or rs.open vinod_yadav1919 Crystal Reports 0 January 3rd, 2005 10:26 AM



All times are GMT -4. The time now is 07:00 PM.


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