Alpha Video Training
Results 1 to 4 of 4

Thread: SQL Question

  1. #1
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,772

    Default SQL Question

    I have 2 tables: customers and charges. I want to produce a grid that has 3 columns: Customer, Date, Fee

    The date and fee come from the charges table.

    I want to produce a grid that lists ALL the customers, but displays Date and Fee ONLY if the date is within a date range of say, 11-01-2017 thru 11-30-2017.

    So it should look like this:

    Customer Date Fee
    Adams, John 11-3-17 200.00
    Franks, Amy -- nothing shown in Date and Fee because this customer has no charges within november
    Small, Cindy 11-1-17 150.00

    How can I do this?
    Gary S. Traub, Ph.D.


  2. #2
    "Certified" Alphaholic
    Real Name
    Frank Bicknell
    Join Date
    Feb 2010
    Location
    39.1915° N, 84.4348° W
    Posts
    2,467

    Default Re: SQL Question

    The easiest is to create a view with a join statement.
    Leave off the where clause if you want to return all of the records and filter them locally in the grid

    So it would be something like this MySQL:

    Code:
    SELECT c.customer,
    ch.date,
    ch.fee
    from customers c
    JOIN charges ch on charges_customer_foreign_key_id = customer_key_id
    WHERE ch.date BETWEEN '2017-11-01'
    AND '2017-11-31'
    ps. I would avoid using field names like 'date' since this is used in code in various fashions. I would use something like 'chg_date' instead.
    Win 10 64 Development, Win 7 64 WAS 11-1, 2, Win 10 64 AA-1,2, MySql, dbForge Studio The Best MySQL GUI Tool IMHO. http://www.devart.com/dbforge/mysql/studio/

  3. #3
    Member
    Real Name
    Mark Bierer
    Join Date
    May 2012
    Location
    Tucson, AZ
    Posts
    45

    Default Re: SQL Question

    Something Like

    SELECT CU.NAME, CH.DATE, CH.FEE
    FROM CUSTOMERS CU
    LEFT OUTER JOIN CHARGES CH ON CU.CUSTOMERID = CH.CUSTOMERID AND CH.DATE BETWEEN :whatStartDate AND :whatEndDate

  4. #4
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,772

    Default Re: SQL Question

    Thank you SO much!!!
    Gary S. Traub, Ph.D.


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
  •