Dudas sobre el uso de restricciones adicionales en la clausula ON del INNER

[AlconNegro]

Buenas gente, deseo consultarles sobre la forma en que estoy obteniendo cierto resultado pues he encontrado 2 formas de hacerlo y si bien una me parece la mas natural, por lo que he leido no es del todo 'correcta' y temo que al llenarse las tablas con datos en algun momento lo que retorne no sea lo que estoy esperando (de ante mano mis disculpas por mi falta de profundidad en mis conocimientos, estoy trabajando para subsanarlo). Llendo al emplo concreto tengo estas 2 tablas:

Código:

CREATE TABLE `it_tickets_prioridades` (
  `idPrioridad` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `denom` VARCHAR(20) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`idPrioridad`)
)ENGINE=InnoDB
AUTO_INCREMENT=5 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

Código:

CREATE TABLE `it_tickets_clasificacionprioridades` (
  `idClasificacionPrioridad` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `idClasificacion` INTEGER(11) UNSIGNED NOT NULL,
  `idPrioridad` INTEGER(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`idClasificacionPrioridad`)
)ENGINE=InnoDB
AUTO_INCREMENT=6 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

La idea es tener un conjunto diferente de prioridades para cada clasificación, por lo cual en la pagina donde se asignan siempre necesito la lista de prioridades y que me retorne información para las que si estan disponibles para una clasificación dada (en mis ejemplos sera la de idClasificacion = 1).

Mi solución inical fue entonces:

Código:

SELECT 
  it_tickets_clasificacionprioridades.idClasificacionPrioridad,
  it_tickets_clasificacionprioridades.rev,
  it_tickets_prioridades.idPrioridad,
  it_tickets_prioridades.denom
FROM
  it_tickets_prioridades
  LEFT OUTER JOIN it_tickets_clasificacionprioridades ON
  (
  	it_tickets_prioridades.idPrioridad = it_tickets_clasificacionprioridades.idPrioridad AND
    it_tickets_clasificacionprioridades.idClasificacion = 1
  )

Pero no encontre en ningún lado que recomienden que dentro de la restricción de ON del INNER pueda poner mas condiciones que el campo que se usa como punto de unión; de forma que esta solución (que me parecio mucho mas compleja) parece ser la mas 'correcta':

Código:

SELECT 
  clasifPrior.idClasificacionPrioridad,
  it_tickets_prioridades.idPrioridad,
  it_tickets_prioridades.denom
FROM
  it_tickets_prioridades
  LEFT OUTER JOIN 
  (
  	SELECT 
    	it_tickets_clasificacionprioridades.idPrioridad,
	    it_tickets_clasificacionprioridades.idClasificacionPrioridad 
    FROM it_tickets_clasificacionprioridades
    WHERE 
    	it_tickets_clasificacionprioridades.idClasificacion = 1
  ) AS clasifPrior ON
  (
  	it_tickets_prioridades.idPrioridad = clasifPrior.idPrioridad
  )

Les agradeceria mucho si alguien me puede ayudar a lograr que estoy interpretando mal; espero haber sido lo suficientemente claro al expresar mi duda; desde ya muchas gracias.

[Teosanchez]

Hola.
Realmente la solución buena es la que a ti te parece más compleja, es decir relacionar las dos tablas por el campo PK de una con el FK de la otra.
Si necesitas ponerle más condiciones al JOIN lo puedes hacer en la cláusula WHERE, donde puedes poner filtros sobre las dos tablas.

Esta es una buena práctica de programación que sio la sigues te evitará un sin fin de problemas.

Así quedaría tu ejemplo:

Código MySQL:

SELECT 
  it_tickets_clasificacionprioridades.idClasificacionPrioridad,
  it_tickets_clasificacionprioridades.rev,
  it_tickets_prioridades.idPrioridad,
  it_tickets_prioridades.denom
FROM
  it_tickets_prioridades
  LEFT OUTER JOIN it_tickets_clasificacionprioridades ON
  (     it_tickets_prioridades.idPrioridad = it_tickets_clasificacionprioridades.idPrioridad )
WHERE 
it_tickets_clasificacionprioridades.idClasificacion = 1
 

Saludos

[AlconNegro]

Ante todo muchas gracias por tomarte el tiempo necesario para responderme Teosanchez; ahora bien,
esa solución que propones la habia experimentado y la respuesta que se obtiene es:

idClasificacionPrioridad; idPrioridad; rev; denom
1; 0; 1; baja
2; 0; 4; solo comentarios

mientras que las que obtengo con los ejemplos que envie es

idClasificacionPrioridad; idPrioridad; rev; denom
1; 0; 1; baja
null; null; 2; media
null; null; 3; alta
2; 0; 4; solo comentarios

Esto me sirve asi pues esta respuesta me permite implementar una solución de programación directa de la lectura
del resulset, mientras que la otra me obligaria a tener que cruzar la información con una lectura separada de la tabla
de prioridades (o sea preciso el dato idClasificacionPrioridad p ejecutar la logica de anular un vinculo entre una
clasificacion y una prioridad y el idPrioridad para la de crearlo).

Teniendo en cuenta esto y tus observaciones me queda claro entonces que la solución correcta va por el lado de
la 2da consulta que propuse; ahora bien, me podrias decir que tipo de problemas me puede generar la
solución que es mas 'simple'? sabes si el motor de mysql es el unico que tolera eso? (me da curiosidad saber por
que funciona y cual seria la utilidad real para la que lo pensaron (puede ser un ejemplo diferente del que propuse
si te resulta mas comodo para explayarte)).

1000 disculpas por tantas preguntas, pero en verdad quiero lograr un conocimiento mas profundo de esto
mas alla de la solución que le aplique a este problema puntualmente; nuevamente, muchas gracias por
tu tiempo.

Atte Fernando

[Teosanchez]

Hola Fernando
Vayamos por partes:

Si la respuesta que necesitas es la segunda que propones, entonces es que necesitas hacer un RIGHT JOIN en lugar de un LEFT JOIN.

En cuanto a los problemas que te puede dar la otra forma de hacerlo pueden ser, en primer lugar, de rendimiento porque las relaciones se establecen entre la PK de una tabla con un campo que es FK de la otra y eso hace que el motor de BD cree automáticamente un índice por esos campos, lo que acelera los accesos.

Por otra parte, también te dificulta escribir y depurar las consultas (que no es poco).

Finalmente una recomendación: Lee sobre diseño de bases de datos y normaliza hasta la tercera forma normal todas tus bases de datos. Te evitarás un sinfín de problemas.

[AlconNegro]

Bien, ahi encontre material sobre las 1NF ... 5NF y me doy cuenta que hasta ahora me he manejado con mucho conocimiento
empirico (que me ha sido util hasta el moento), pero es evidente que ahora preciso asimilar otra parte de mas teorica y ver como
aplica antes que lo que estoy armando entre en producción y me cause cause problemas inesperados (por suerte hay margen).

Muchas gracias por tu tiempo TeoSanchez.

Atte Fernando