Problema con consulta anidada

[mercucho1]

Buenas!! Pues tengo un problemilla con una consulta a la base de datos,... A grandes rasgos tengo:

* Tabla material (id, nombre)
* tabla historial_material (id, idmaterial, fechacreacion, estado)

Cada vez que cambia el estado del material (puede estar ocupado, roto, disponible,...), inserto una linea en la tabla historial_material.

El caso es que quiero sacar una lista de material con su estado actual (me baso en el maximo fechacreacion) y no hay manera :(

Lo ideal sería....

Código MySQL:

SELECT m.*, hm.estado FROM MATERIAL m, HISTORIAL_MATERIAL hm
WHERE m.id=hm.idmaterial and hm.id in (
select max(fechacreacion) from HISTORIAL_MATERIAL hm2 where hm2.id = hm.id limit 0,1)
 

Pero el problema está en que no se puede (en MySQL por lo menos) realizar el limit en una consulta interna. ¿Alguien se le ocurre alguna consulta que pueda funcionar?

[jurena]

Prueba así:
SELECT m.id, m.nombre, t1.estado, t1.fechacreacion FROM MATERIAL m INNER JOIN (SELECT idmaterial, estado, fechacreacion FROM HISTORIAL_MATERIAL ORDER BY fechacreacion DESC)t1 on t1.idmaterial = m.id GROUP BY m.id

[mercucho1]

Muchas gracias por tu respuesta! Parece que funciona perfectamente!!

¿podrías explicar un poco el "truco" de esa consulta? Es que me resulta raro al realizar la consulta interna saque todos los valores de la tabla historial, pero despues cuando hace el group by solo coge el primer registro que coincida de la consulta interna ignorando los demas ¿pq pasa eso?

[mercucho1]

Iniciado por jurena

Prueba así:
SELECT m.id, m.nombre, t1.estado, t1.fechacreacion FROM MATERIAL m INNER JOIN (SELECT idmaterial, estado, fechacreacion FROM HISTORIAL_MATERIAL ORDER BY fechacreacion DESC)t1 on t1.idmaterial = m.id GROUP BY m.id

vaya, me he encontrado con otro problema. Resulta que quiero sacar solo el material que este disponible (en mi caso lo he codificado con un int, donde 0 es disponible, 1 ocupado...). Al modificar la consulta para realizar esto, ya no me saca los datos correctos, ya que al tener el select interno todos los registros disponibles, coge los especifique en el where ignorando el orden preestablecido :( ¿alguna sugerencia?

[mercucho1]

Iniciado por mercucho1

vaya, me he encontrado con otro problema. Resulta que quiero sacar solo el material que este disponible (en mi caso lo he codificado con un int, donde 0 es disponible, 1 ocupado...). Al modificar la consulta para realizar esto, ya no me saca los datos correctos, ya que al tener el select interno todos los registros disponibles, coge los especifique en el where ignorando el orden preestablecido :( ¿alguna sugerencia?

antes lo digo, antes lo saco. He vuelto a realizar otra consulta mas sobre los resultados, quedando así...

Código MySQL:

SELECT * FROM
 (SELECT m.id, m.nombre, t1.estado, t1.fechacreacion FROM MATERIAL m INNER JOIN 
    (SELECT idmaterial, estado, fechacreacion FROM HISTORIAL_MATERIAL ORDER BY fechacreacion DESC) t1 
 on t1.idmaterial = m.id GROUP BY m.id) t2
where t2.estado = 1
 

Tiene miga la consulta...

[jurena]

SELECT m.id, m.nombre, t1.estado, t1.fechacreacion FROM MATERIAL m INNER JOIN (SELECT idmaterial, estado, fechacreacion FROM HISTORIAL_MATERIAL WHERE estado = 1 ORDER BY fechacreacion DESC)t1 on t1.idmaterial = m.id WHERE t1.estado = 1 GROUP BY m.id

El estado lo buscas dentro, porque reduces el número de registros en la selección y eliminas una subconsulta, aunque luego tienes que volver a introducirlo en el where final, dada el tipo de cruce con INNER JOIN basado sólo en el idmaterial.
El truco sobre el que preguntabas: cuando MySQL agrupa, sólo devuelve el primer registro de los que comparten el elemento agrupado; yo con el order by desc pongo como primero de cada id el que tiene la última fecha, y al agrupar me conserva sólo ese.