0

I have two tables. One is basic customer information and one is ordering information.

I'm trying to find the max(order date) with the order status of 'placed'or'cancelled'. I don't care if the status if placed or cancelled. I just want the most recent order.

The first table (Info)

 CustomerID     LAST NAME   FIRST NAME
        1              AB          BOB  
        2              BC          ROBERT
        3              AA          JOHN

The second table(order)

CustomerID     Order Date   Order Status
1              12/16/2016   placed
2              8/5/2016     cancelled
1              5/8/2015     cancelled
2              8/9/2016     placed
3              7/15/2016    cancelled
3              8/20/2015    placed

I want the result to be:

CustomerID  FirstName  LastName    OrderDate    OrderStatus
1              AB       BOB        12/16/2016    placed
2              BA       ROBERT     8/9/2016      placed
3              AA       JOHN       7/15/2016     cancelled

Here are my SQL syntax

SELECT distinct Info.CustomerID, Info.Lastname,Info.Firstname
    FROM INFO
    INNER JOIN
    (SELECT
    order.CustomerID, LastOrderDate=max(OrderDate),order.OrderStatus
    FROM Order
    GROUP BY order.CustomerID, order.orderstatus)a
    ON a.CustomerID=Info.CustomerID

This didn't work because it's grouping by order status which give me the max date of each all orderstatus. Then I tried

SELECT distinct Info.CustomerID, Info.Lastname,Info.Firstname, Order.OrderStatus
    FROM INFO, Order
    INNER JOIN
    (SELECT
    order.CustomerID, LastOrderDate=max(OrderDate)
    FROM Order
    GROUP BY order.CustomerID)a
    ON a.CustomerID=Info.CustomerID

This didn't work either because it says the Info.CustomerID could not be bound.

Any helps? Thanks!

CHYP123
  • 1

1 Answers1

0

Can you try the following query:

SELECT c.customerid, c.last_name, c.first_name, o.order_status, MAX(order_date)
FROM customer c JOIN order ON c.customerid = o.customerid
WHERE o.order_status IN ('placed', 'cancelled')
GROUP BY c.customerid, c.last_name, c.first_name, o.order_status;

update

If you don't want the max date by order status then you can remove it from group by, e.g.:

SELECT c.customerid, c.last_name, c.first_name, MAX(order_date)
FROM customer c JOIN order ON c.customerid = o.customerid
WHERE o.order_status IN ('placed', 'cancelled')
GROUP BY c.customerid, c.last_name, c.first_name;

Another update

Following query would return both the records if an order gets placed and cancelled on the same day:

SELECT c.customerid, c.first_name, c.last_name, o.order_date, o.order_status
FROM customer c JOIN
(SELECT o1.customerid, o1.order_date, o1.order_status
FROM `order` o1 JOIN
(SELECT o.customerid, max(o.order_date) as max_date
FROM `order` o
WHERE o.order_status in ('placed', 'cancelled')
GROUP BY o.customerid) o2
ON o1.customerid = o2.customerid and o1.order_date = o2.max_date) o
ON c.customerid = o.customerid;

Here's the SQL Fiddle.

Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102