Sparrow Sparrow - 1 month ago 6
MySQL Question

MYSQL - Find a value with date comparison constraints in a single table but multiple rows for an unique identifier

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
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)
Comments