query to obtain maximum date between several tables

Question

query to obtain maximum date between several tables

Navigation

#1 by (5 votes)
#2 by (1 votes)

4

Is it possible to obtain the most recent date among several tables, given the id of a main table that is in all the other tables? How could I get this?

Table 1 main

id_tabla1 
descripcion

table 2

id_tabla2
fecha
fk_tabla1

table 3

id_tabla3
fecha
fk_tabla1

table 4

id_tabla4
fecha
fk_tabla1

I tried the following:

select MAX(Fecha) as fecha 
  from tabla1 
 INNER JOIN tabla2 on tabla1.id_tabla1 = tabla2.id_tabla1 
 GROUP BY tabla1.id_tabla1

But that is the most recent date of tabla2 . How could I compare then with the others and have the most recent of all?

mysql sql

asked by Alex Fernando Banegas Gonzalez 13.12.2016 в 15:35

source

2 answers

1

If you want to know the maximum date between all the tables you can not make a join between them. Instead, you should get the dates of all of them and get the maximum value. For this, you use UNION ALL :

SELECT MAX(T.fecha) AS 'Fecha_maxima'
FROM (
    SELECT fecha
    FROM tabla2 t2
      INNER JOIN tabla1 t1 ON t1.id = t2.fk_tabla1

    UNION ALL

    SELECT fecha
    FROM tabla3 t3
      INNER JOIN tabla1 t1 ON t1.id = t3.fk_tabla1

    UNION ALL

    SELECT fecha
    FROM tabla4 t4
      INNER JOIN tabla1 t1 ON t1.id = t4.fk_tabla1
) T;

answered by 13.12.2016 в 16:07

Search data in Sql and display them in C # ComboBox Creation of .zip with php

score 5 · Accepted Answer

One idea is to make a join of all the tables, making the max of each date, and then using GREATEST to return the maximum of all the maximums:

select greatest(
         coalesce(max(t2.fecha), 0), 
         coalesce(max(t3.fecha), 0), 
         coalesce(max(t4.fecha), 0)) as max_fecha
  from tabla1 t1
  left join tabla2 t2
    on t2.fk_tabla1 = t1.id_tabla1
  left join tabla3 t3
    on t3.fk_tabla1 = t1.id_tabla1
  left join tabla4 t4
    on t4.fk_tabla1 = t1.id_tabla1

Edit

I just realized that in your query you include GROUP BY tabla1.id_tabla1 , which seems to indicate that you want the maximum date per id_tabla1 . If this is the case, simply add clause GROUP BY to the query:

select t1.id_tabla1,
       greatest(
         coalesce(max(t2.fecha), 0), 
         coalesce(max(t3.fecha), 0), 
         coalesce(max(t4.fecha), 0)) as max_fecha
  from tabla1 t1
  left join tabla2 t2
    on t2.fk_tabla1 = t1.id_tabla1
  left join tabla3 t3
    on t3.fk_tabla1 = t1.id_tabla1
  left join tabla4 t4
    on t4.fk_tabla1 = t1.id_tabla1
 group by t1.id_tabla1

Issue 2

If you want to know which table the maximum value came from, it is a bit more complicated, but you can, using an expression CASE :

select t1.id_tabla1,
       greatest(
         coalesce(max(t2.fecha), 0), 
         coalesce(max(t3.fecha), 0), 
         coalesce(max(t4.fecha), 0)) as max_fecha,
       case when max(t2.fecha) = greatest(
                                   coalesce(max(t2.fecha), 0), 
                                   coalesce(max(t3.fecha), 0), 
                                   coalesce(max(t4.fecha), 0))
            then 'TABLA2'
            when max(t3.fecha) = greatest(
                                   coalesce(max(t2.fecha), 0), 
                                   coalesce(max(t3.fecha), 0), 
                                   coalesce(max(t4.fecha), 0))
            then 'TABLA3'
            when max(t4.fecha) = greatest(
                                   coalesce(max(t2.fecha), 0), 
                                   coalesce(max(t3.fecha), 0), 
                                   coalesce(max(t4.fecha), 0))
            then 'TABLA4'
       end as nombre_tabla
  from tabla1 t1
  left join tabla2 t2
    on t2.fk_tabla1 = t1.id_tabla1
  left join tabla3 t3
    on t3.fk_tabla1 = t1.id_tabla1
  left join tabla4 t4
    on t4.fk_tabla1 = t1.id_tabla1
 group by t1.id_tabla1

Of course, it is possible that the maximum value is the same in more than one table. In that case, it will return one of the tables.