I have the typical master detail table cita
and detalles_cita
where detalles_cita
it contains the cita_id
and the fecha
of cita
.
cita
+----+---------+
| id | nombre |
+----+---------+
| 1 | cita 01 |
| 2 | cita 02 |
| 3 | cita 03 |
| 4 | cita 04 |
+----+---------+
detalles_citas
+-----+---------+------------+
| id | cita_id | fecha |
+-----+---------+------------+
| 1 | 1 | 2019-11-01 |
| 2 | 1 | 2019-11-02 |
| 3 | 1 | 2019-11-03 |
| 4 | 1 | 2019-11-22 |
| 5 | 1 | 2019-11-13 |
| 6 | 1 | 2019-11-06 |
| 7 | 1 | 2019-11-07 |
| 8 | 1 | 2019-11-10 | -> último reg
| 9 | 2 | 2019-11-05 |
| 10 | 2 | NULL |
| 11 | 2 | NULL |
| 12 | 2 | 2019-11-21 |
| 13 | 2 | 2019-11-22 | -> último reg
| 14 | 2 | NULL |
| 15 | 2 | NULL |
| 16 | 2 | 2019-11-25 |
| 17 | 3 | 2019-11-05 |
| 18 | 3 | 2019-11-07 |
| 19 | 3 | 2019-11-22 |
| 20 | 3 | 2019-11-12 | -> último reg
+-----+---------+------------+
I am looking to obtain given an exact date of the appointment, date_searched and last_reg_date_appointment where ultimo_reg_fecha_cita
is the last date recorded with the date of that appointment
Example of what I should get for the day 2019-11-22
+--------+---------------+-----------------------+
| cita | fecha_buscada | ultimo_reg_fecha_cita |
+--------+---------------+-----------------------+
| cita01 | 2019-11-22 | 2019-11-10 |
| cita02 | 2019-11-22 | 2019-11-22 |
| cita03 | 2019-11-22 | 2019-11-12 |
+--------+---------------+-----------------------+
Another example of what you should get for the day 2019-11-06
+--------+---------------+-----------------------+
| cita | fecha_buscada | ultimo_reg_fecha_cita |
+--------+---------------+-----------------------+
| cita01 | 2019-11-06 | 2019-11-10 |
+--------+---------------+-----------------------+
I think the best thing would be to first join the tables and to get the last date make a subquery sorting by the column
id
, I understand that this column has an autoincrementer, so something like this should work:I do not understand your query, there are several tables that you have not mentioned, what I have given you is only for your question regarding the two tables that you comment on.