SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: QUERY help need

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

    1-Get 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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by pearl_123 View Post
    so i want only first one
    please define "first"
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    ====================
    We have two address for each customer in address table so first address(a_id==1) is the shipping address and second address(a_id==2) is buyer address so i want to first address mean shipping address


    3-date of first order
    =============

    Its mean that the first order purchase date of the client.

    thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, here's 1 and 2 --
    Code:
    SELECT cust.c_id       -- 1
         , cust.c_fname    -- 1
         , cust.c_email    -- 1
         , addr.a_address  -- 2
      FROM customer AS cust
    INNER
      JOIN ( SELECT c_id
                  , MIN(a_id) AS ship
               FROM addresses
             GROUP
                 BY c_id ) AS aaaa
        on aaaa.c_id = cust.c_id
    INNER
      JOIN addresses AS addr
        ON addr.c_id = aaaa.c_id
       AND addr.a_id = aaaa.ship
    from this, you should be able to figure out how to do 3

    i'm not going to do any more for you since this is obvioulsy a homework assignment
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply.

    Can you please send the full query with orders information

    thanks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by pearl_123 View Post
    Can you please send the full query with orders information
    no
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    ask your manager for help
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •