Triple_6 Triple_6 - 1 month ago 15
MySQL Question

SQL Query to get nearest previous and next date?

i've got problem to combine SQL querry that should show me nearest (previous and next) date from

CURDATE()
.
I've got table with customers and table witch events

table with customers:

konto_odbiorcy | name
2 | Sony LTD
3 | Panasonic LTD
ect...


This is what i've got in table with events:

number | date
2 | 2016-11-01
2 | 2016-11-06
2 | 2016-11-14
3 | 2016-11-02
3 | 2016-11-21
3 | 2016-11-26


i need to know what is the last past date and next nearest future date like:

number | date past | date future
2 | 2016-11-06 | 2016-11-14
3 | 2016-11-02 | 2016-11-21


As you see, for
number=3
show me
2016-11-21
not
2016-11-26


when i want show past nearest date:

SELECT number, date AS past
FROM 3ce_event
WHERE date <= CURDATE()


for next event:

SELECT number, date AS future
FROM 3ce_event
WHERE date >= CURDATE()


now how to compare this two with my customers table in one SQL querry?

EDIT

I changed my querry to Mike answer:

select number, klienci_ax_all.nazwa, miasto,
max(case when date<=curdate() then date end) as date_past,
min(case when date>=curdate() then date end) as date_future
from 3ce_event
INNER JOIN
klienci_ax_all
ON
3ce_event.number=klienci_ax_all.konto_odbiorcy
group by konto_odbiorcy


looks really good, but i had problem with my customers table (
klienci_ax_all
). If for one or more customers there is nothing in
3ce_event
table, i dont have this customer as
Null
date_past and
Null
date_future.

I need full list of customers from
klienci_ax_all
then past and future data if exist... any help?

Answer
select number,
       max(case when date<=curdate() then date end) as date_past,
       min(case when date>=curdate() then date end) as date_future
  from event
 group by number