Sparrow - 1 year ago 47

MySQL Question

I have given plenty of thoughts on this & have spent a lot of time, but couldn't go anywhere.

I have a table like below, lets call this product table.

`customer_id product_id Type order_date completed_date`

123 A1 X 11/07/2016 14/07/2016

456 A2 X 12/07/2016 15/07/2016

789 A3 X 13/07/2016 16/07/2016

123 A4 Y 15/07/2016 17/07/2016

456 A5 Y 16/07/2016 18/07/2016

789 A6 Y 17/07/2016 19/07/2016

The objective is to find out the customer_id where the order_date of order type Y is the next day of the order type X of the same customer, i.e. 123 is the customer whose X order is completed on 14th July & the Y order is placed on the next date.

I have tried giving the same table two aliases and joining them, but I am doing it wrong and the results are not correct. Please help.

Answer Source

```
select p.customer_id
from product_table p
join product_table p1 on p1.customer_id = p.customer_id and p1.type = 'Y' and p1.order_date = date_add(p.completed_date, interval 1 day)
where p.`type` = 'X'
```

result

```
+-------------+
| customer_id |
+-------------+
| 123 |
| 456 |
| 789 |
+-------------+
3 rows in set (0.00 sec)
```