SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please try this --
    Code:
    SELECT customers.cust_email
      FROM customers
    INNER
      JOIN cust_packages
        ON cust_packages.cpack_usernr = customers.cust_custnr
    GROUP
        BY customers.cust_email
    HAVING COUNT(cust_packages.cpack_usernr) = 
           COUNT(NULLIF(COALESCE(cpack_canceldate,'0000-00-00'),'0000-00-00')
    i tried to consider also the scenario where a customer has two accounts that are cancelled

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937,

    Thanks for your time.

    I am getting this message error:

    Server: localhost - Database: username_database - Table: customers
    Error

    SQL query: Documentation

    SELECT customers.cust_email
    FROM customers
    INNER
    JOIN cust_packages ON cust_packages.cpack_usernr = customers.cust_custnr
    GROUP
    BY customers.cust_email
    HAVING COUNT( cust_packages.cpack_usernr ) = COUNT( NULLIF( COALESCE( cpack_canceldate, '0000-00-00' ) , '0000-00-00' )
    LIMIT 0 , 30

    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 10

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    ooops, my bad, add another closing parenthesis before LIMIT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Wow thanks, it worked.

    The faster reply (and correct reply) that I see in forums.

    I will continue accessing this community.

    What I need now is a php script or simple code (I can use a Windows Program too, if necessary) to send a message to all these emails of cancelled customers.

    If I'm not abusing, do you know any script for this?

    Thanks.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't do scripts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was not talking about you do scripts, only if you knew one script to suggest.

    But no problem, you help me a lot, thanks again.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •