[SOLUCIONADO] Sacar estado máximo con dos tablas relacionadas

[chechitoo]

Hola Foreros y Expertos

Soy novatillo en sql y en este foro. Os presento mi problema y desde ya mil GRACIAS,

Tengo dos tablas;

ESTADOS
ID_ESTADO ESTADO FECHA
1 abierta 26/01/2012
1 tratando 27/01/2012
1 tratando 28/01/2012
1 cerrada 29/01/2012
2 abierta 27/01/2012
etc

CLIENTES
ORT NOMBRE APELLIDOS
1 ana martín

Estas dos tablas están relacionadas por ID_ESTADO <-> ORT

La tabla estados contiene todos los estados que puede tener un ID_ESTADO, incluso algunos estados repetidos pero siempre con fecha
diferente.
La tabla CLIENTES sólo tiene un cliente por cada "ORT"

Lo que necesito es, relacionar las dos tablas y que para cada ORT, me saque la información el último estado. Es decir para el
ID_ESTADO 1 sería

1 ana martin 1 cerrada 29/01/2012

Buscando por el foro he podido avanzar algo;

SELECT CLIENTES.ORT, CLIENTES.NOMBRE ESTADOS.ESTADO, MAX(ESTADOS.FECHA) as RECORD FROM
(CLIENTES INNER JOIN ESTADOS ON CLIENTES.ORT = ESTADOS.ID_ESTADO)
GROUP BY ESTADOS.ID_ESTADO

Esto saca el última fecha por cada cliente pero el estado no concuerda con la fecha


Me estoy volviendo bastante loco con el asunto, por lo que agradezco la ayuda.
Muchas Gracias y enhorabuena por el foro

[gnzsoloyo]

Lo que pasa es que estás perdiendo la perspectiva de lo que buscas:

Lo que necesito es, relacionar las dos tablas y que para cada ORT, me saque la información el último estado

Entonces no se debe agrupar por estado, sino por ORT...

Código MySQL:

SELECT 
    C.ORT, 
    C.NOMBRE 
    E.ESTADO, 
    MAX(E.FECHA) RECORD 
FROM
    CLIENTES C INNER JOIN ESTADOS E ON C.ORT = E.ID_ESTADO
GROUP BY C.ORT
 

De todos modos, no se comprende la relación entre el ORT (que no se ve qué significa) con el Estado (que tampoco se sabe qué es).
Falta info para saber si la consulta está bien ideada.

[chechitoo]

Hola gnzsoloyo, gracias por tu rápida respuesta.

He probado lo que me indicas y me sigue saliendo la última fecha para cada cliente pero el ESTADO de la tabla ESTADOS incorrecto. El resultado que me sale es

1 ana martin abierta 29/01/2012

cuando debería de salir
1 ana martin cerrada 29/01/2012

ORT es el nº de cliente. A cada nuevo cliente se le asigna un nº de cliente que se llama ORT en la tabla CLIENTES.
Ese mismo nº cliente se llama ID_ESTADO en la tabla ESTADOS...

Por favor, si no me he explicado correctamente dímelo y te doy los detalles que necesites.
Gracias!

[gnzsoloyo]

La verdad, el problema es un diseño defectuoso, que sólo se percibe cuando tienes algo de experiencia en este tipo de detalles.
El problema máximo es que poner un atributo "estado" como cadena de caracteres es un error conceptual. Los "estados" deben ser siempre numéricos para poder jerarquizarlos. Las descripciones de los estados, esos pueden ser cadenas descriptivas. De ese modo lo puedes manejar mucho más fácil, sea con una tabla parametrizada de referencia, o bien usando campos ENUM.

Pero así como está, no se puede hacer sin darle una mejor parametrización a la consulta.

Esto tal vez sirva:

Código MySQL:

SELECT ORT, NOMBRE, ESTADO, RECORD
FROM 
    (SELECT 
        C.ORT, 
        C.NOMBRE 
        E.ESTADO, 
        MAX(E.FECHA) RECORD 
    FROM
        CLIENTES C INNER JOIN ESTADOS E ON C.ORT = E.ID_ESTADO
    ORDER BY FIND_IN_SET(E.ESTADO, 'abierta, tratando, cerrada') DESC) T1
GROUP BY ORT
 

[leonardo_josue]

Otra forma para obtener este resultado sería así:

Código MySQL:

mysql> SELECT c.ort, c.nombre, c.apellidos, e.estado, e.fecha FROM
    -> clientes C
    -> INNER JOIN estados E ON c.ort = e.id_estado
    -> INNER JOIN
    -> ( SELECT id_estado, MAX(fecha) max_fecha
    ->   FROM estados
    ->   GROUP BY id_estado) T
    -> ON T.id_estado = E.id_estado AND T.max_fecha = E.fecha;
+------+--------+-----------+---------+------------+
| ort  | nombre | apellidos | estado  | fecha      |
+------+--------+-----------+---------+------------+
|    1 | ana    | martin    | cerrada | 2012-01-29 |
+------+--------+-----------+---------+------------+
1 row in set (0.00 sec)
 

El truco está en la subconsulta del segundo INNER JOIN, obtienes la MAX(FECHA) para cada estado y la utilizas para filtrar la información... Haz la prueba, tanto la consulta de gnzsoloyo como la mía deberían de funcionar.

Saludos
Leo.

[chechitoo]

Gracias por la respuesta. He probado ambas soluciones y ambas salen bien los datos que quiero.

Al final he usado la tuya leonardo_josue.

Muchas Gracias!!