Thread: QUERY help need

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    67
    Rep Power
    0

    QUERY help need


    Hi to all,


    I have 3 table customers,addresses,orders
    Code:
    Customer table
    ===========
    
    c_id      c_fname              c_email
    ---------------------------------------
     1           a                     a@gmail.com
     2           b                     b@gmail.com       
    
    
    Addresses table
    ============
    
    a_id        c_id         a_address 
    ---------------------------------------
    1             1              xyz
    2             1              xyz
    3             2              abc
    4             2              abc
    
    
    Orders table
    ===========
    
    o_id      c_id         o_total        o_date
    --------------------------------------
    1            1             10            2013-01-01  
    2            1             20            2013-02-02  
    3            1             30            2013-03-03   
    4            2             40            2013-01-01
    I want to
    1-All the customer information from customers table
    2-First address of each customer not the both because addresses table has shipping and buyer address which can be same so two entries will be there in address so i want only first one

    3-date of first order
    4-Total amount purchase
    5-Amount purchased in last 30 days
    6-Amount purchased in last 60 days
    7-Amount purchased in last 90 days


    Finale result will be
    Code:
    c_id  c_name    c_email           a_id   a_address   total_purchase   first_ord            last_purchase    30days_purchase   60days
    -------------------------------------------------------------------------------------------------------------------------
    1       a           a@gmail.com     1       xyz             60                  2013-01-01     2013-03-03          30                        50
    2       b           b@gmail.com     3       abc             40                  2013-01-01      2013-01-01         NULL                     NULL
    Any help
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by pearl_123
    so i want only first one
    please define "first"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    2
    Rep Power
    0

    hope this helps


    SELECT
    customer.c_id,
    customer.c_fname,
    customer.c_email,
    first_address.a_id,
    first_address.a_address,
    order.first_order,
    order.last_purchase,
    order.Total_amount_purchase,
    order.30day_purchase,
    order.60day_purchase,
    order.90day_purchase

    FROM
    customer
    INNER JOIN
    (
    SELECT
    address.c_id,
    address.a_id,
    address.a_address
    FROM address
    INNER JOIN
    (
    SELECT
    address.c_id,
    MIN(address.a_id) a_id
    FROM address
    GROUP BY 1
    ) f_address
    ON address.c_id = f_address.c_id
    AND address.a_id = f_address.a_id
    ) first_address
    ON customer.c_id = first_address.c_id

    INNER JOIN
    (
    SELECT
    c_id,
    MIN(o_id) first_order,
    MAX(o_id) last_purchase,
    SUM(o_total) Total_amount_purchase,
    SUM(CASE WHEN o_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
    THEN o_total
    ELSE 0
    END ) 30day_purchase,
    SUM(CASE WHEN o_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 60 DAY) AND CURDATE()
    THEN o_total
    ELSE 0
    END ) 60day_purchase,
    SUM(CASE WHEN o_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 90 DAY) AND CURDATE()
    THEN o_total
    ELSE 0
    END ) 90day_purchase
    FROM order
    GROUP BY 1
    ) order_main
    ON customer.c_id = order.c_id

IMN logo majestic logo threadwatch logo seochat tools logo