QUERY help need

Databases
1

Hi to all,

I have 3 table customers,addresses,orders



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



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

please define “first”

3

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

okay, here’s 1 and 2 –

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

5

Thanks for the reply.

Can you please send the full query with orders information

thanks

6

no :slight_smile:

7

Hi r937 ,

Its not assigment its project requirement and i am late now can you please give me the solution with this query to find the last 30 day’s,60 day’s and 90 day’s order total of each customer

waiting

8

ask your manager for help