LEFT JOIN con dos argumentos en el ON

[m87laucha]

Buenas, estoy teniendo un problema con una consulta que espero alguien pueda ayudarme. Es una mezcla de JOIN con otras restricciones.

Les describo:

Tablas:

TRABAJO
idtrabajo(PK)
fecha

MATERIAL_TRABAJO
idmaterial(FK)
idtrabajo(FK)
cantidad

MATERIAL
idmaterial(PK)
nombre
idcategoria(FK)

Quiero obtener lo siguiente:

Quiero obtener todos los trabajos junto a los materiales que sean de una categoría específica.
La consulta que estaba intentando hacer era la siguiente:

SELECT T.idtrabajo, M.idmaterial, MT.cantidad, M.nombre
FROM trabajos AS T
LEFT JOIN materiales_trabajos AS MT ON(MT.idtrabajo = T.idtrabajo)
LEFT JOIN materiales AS M ON(M.idmaterial = MT.idmaterial AND M.idcategoria = 1)

Pero no me filtra los materiales categoría 1 como yo quiero.

También probé con la siguiente manera:

SELECT T.idtrabajo, M.idmaterial, MT.cantidad, M.nombre
FROM trabajos AS T
LEFT JOIN materiales_trabajos AS MT ON(MT.idtrabajo = T.idtrabajo)
LEFT JOIN materiales AS M ON(M.idmaterial = MT.idmaterial)
WHERE M.idcategoria = 1

Pero no me muestra aquellos trabajos que no tienen materiales de es categoría.

Espero que alguien pueda ayudarme, la base de datos desgraciadamente no la armé yo y no la puedo modificar sino lo haría con muchas ganas porque no es está bien normalizada.

Saludos

[K-SuMa]

SELECT T.idtrabajo, M.idmaterial, MT.cantidad, M.nombre
FROM trabajos AS T, materiales_trabajos AS MT, materiales AS M
where MT.idtrabajo = T.idtrabajo and M.idmaterial = MT.idmaterial AND M.idcategoria = 1

El left join lo utilizas cuando quieres mostrar los datos de la tabla izquierda asi no matchen con las otras tablas en este caso como quieres mostrar los trabajos de una categoria especificada las tres tablas siempre tienen q cruzar por lo q un where es lo mas indicado.. cualquier consulta estamos para ayudarnos...

Salu2,

[m87laucha]

Gracias por la respuesta, pero de esa forma tampoco puedo solucionarlo ya que el where rompe el producto cartesiano en ambas direcciones y en mi caso existen trabajos que no tienen un material categoría 1 y con esa consulta son excluidos.

Lo que necesito, es que aparezcan todos los trabajos incluidos los que tienen material categoria 1 y los que no tiene también.
Dicho de otra manera, quiero todos los trabajos y aquellos que tiene material categoria 1 tambien infrome el material.

Saludos

[jurena]

SELECT T.idtrabajo, M.idmaterial, MT.cantidad, M.nombre, M.idcategoria
FROM trabajos AS T
LEFT JOIN materiales_trabajos AS MT ON(MT.idtrabajo = T.idtrabajo)
LEFT JOIN materiales AS M ON(M.idmaterial = MT.idmaterial)

ESto debería mostrártelos todos, tengan o no idcategoria 1; y cuando no tienen ninguna categoría, te mostrará null

con IFNULL podrás quitar el null y mostrar lo que quieras

SELECT T.idtrabajo, M.idmaterial, MT.cantidad, M.nombre, IFNULL(M.idcategoria,'sin categoría') id_categoria
FROM trabajos AS T
LEFT JOIN materiales_trabajos AS MT ON(MT.idtrabajo = T.idtrabajo)
LEFT JOIN materiales AS M ON(M.idmaterial = MT.idmaterial)

[m87laucha]

Esactamente... esa consulta me muestra todo. Pero quiero filtrar solo los de categoría 1. Ahi es cuando se presenta el problema y mas teniendo en cuenta que cada trabajo puede tener varios materiales pero de categoría 1 solo un material.

me parece que lo voy a tener que solucionar agregando un campo mas en la tabla en cuestión... pero no quiero hacerlo hasta no estar seguro.

Que me aconsejan?

[jurena]

Iniciado por m87laucha

Buenas, estoy teniendo un problema con una consulta que espero alguien pueda ayudarme. Es una mezcla de JOIN con otras restricciones.

Les describo:

Tablas:

TRABAJO
idtrabajo(PK)
fecha

MATERIAL_TRABAJO
idmaterial(FK)
idtrabajo(FK)
cantidad

MATERIAL
idmaterial(PK)
nombre
idcategoria(FK)

SELECT trabajo.idtrabajo, t1.categoria FROM trabajo LEFT JOIN (SELECT distinct T.idtrabajo as trabajo, M.idcategoria as categoria
FROM trabajo AS T
INNER JOIN material_trabajo AS MT ON(MT.idtrabajo = T.idtrabajo)
INNER JOIN materiales AS M ON(M.idmaterial = MT.idmaterial)
WHERE M.idcategoria = 1)t1 ON trabajos.idtrabajo = t1.trabajo LEFT JOIN material_trabajo ON material_trabajo.idtrabajo = trabajo.idtrabajo LEFT JOIN material on material.idmaterial = material_trabajo.idmaterial


La idea es que primero localices mediante inner join los trabajos en los que se usan esos materiales, y luego que hagas un cruce de tablas en el que se muestren todos los trabajos, también aquellos en los que no se han usado esos materiales.

No lo he probado, pero esa es la idea.

[m87laucha]

Ufffff.... muchas gracias Jurena me has tirado la pinta para solucionar el problema. En tu consulta encontré la forma de lograr lo que quería, les muestro lo que quedó de la consulta para que vean la magnitud de la misma ya que las 3 tablas que describí eran solo el centro del problema.

Ahora falta probar como se comporta con mas de 20.000 registros, espero no tener problema con el servidor.


__________________________________________________ _______________

SELECT P.idpedido, B.descripcion AS brigada,T.nrolinea, EP.descripcion as estado,
P.equipo,T.horabaremo, P.nroacta, t1.equipo AS 'Equipo Instalado',
CONCAT('No') AS kit,
DATE_FORMAT(P.fechacumplido, '%d/%m/%Y') AS fechacumplido,
DATE_FORMAT(P.fechaconfirmado, '%d/%m/%Y') AS fechaconfirmado
FROM pedidos AS P,estadospedidos AS EP, brigadas AS B
RIGHT JOIN trabajos AS T
ON (T.brigadas_idbrigada = B.idbrigada)
LEFT JOIN
(
SELECT T.idtrabajo, CONCAT(M.idmaterial,' - ',M.nombre) AS equipo
FROM pedidos AS P,trabajos AS T
INNER JOIN materiales_trabajos AS MT
ON(MT.trabajos_idtrabajo = T.idtrabajo)
INNER JOIN materiales AS M
ON(M.idmaterial = MT.materiales_idmaterial)
WHERE M.categoriasmateriales_idcategoria = 1
AND P.trabajos_idtrabajo = T.idtrabajo
AND P.kit = 0
)
t1
ON T.idtrabajo = t1.idtrabajo
WHERE P.trabajos_idtrabajo = T.idtrabajo
AND P.kit = 0
AND EP.idestadopedido = P.estadospedidos_idestadopedido

__________________________________________________ _______________

Pues eso fué todo... un buen problemita para poner a prueba la lógica el el conocimiento de base de datos.

Muchas gracias por la ayuda.

Saludos