LEFT JOIN con condiciones en ambas columnas, sentencia algo compleja..

[barbel]

Buenas, antes de nada gracias de nuevo a la comunidad por echar una mano. Expongo mi problema y los pasos que he ido dando para solucionarlo, aunque aun no he llegado a buen termino.

Mi pagina web tiene, entre otras, 2 tablas que necesito relacionar. En una de ellas, guardo la ficha de cada producto, con sus campos etc etc, uno de estos campos es el precio.
Por otro lado, tengo otra tabla llamada preciopromocional, donde almaceno como su nombre indica precios promocionales de los productos. Estos precios vienen determinados por una fecha de inicio de promoción y una fecha de fin de promoción. Estos registros están en una tabla distinta y no deben ser nunca eliminados, debido a las operaciones que se realizan con ellos a posteriori (estadisticas de ventas durante la promoción, beneficios obtenidos durante el periodo promocional frente un mismo periodo fuera de promoción, etc..) Por lo tanto en esta tabla tenemos 4 campos, un id del producto, un precio promocional, una fecha de inicio de promoción y una fecha de fin de promoción.

Cuando el programa carga un producto, debe escoger el precio promocional si se encuentra dentro de las fechas de una promoción y sino, el precio normal.

Esto en php seria algo completamente sencillo, o utilizando 2 setencias SQL, pero por problemas de memoria (que no entrare en detalles ahora) lo más optimo seria realizar esto en una misma sentencia SQL. Creo que es posible y he ido avanzando en ello, pero no logro conseguir que funcione con todas las eventualidades.

Para verlo mejor adjunto un par de capturas de pantalla de ambas tablas:

tabla "productos":


tabla "preciopromocional":


Por lo tanto nos encontramos en esta situación, en la primera tabla tenemos los productos, y en la segunda los precios promocionales vinculados a esos productos. Puede ser que tenga 1 precio promocional y que este en vigor (la fecha actual este comprendida entre la fecha de inicio y fin), que tenga varios precios promocionales (aunque solo 1 en vigor), que no tenga ningun precio promocional y que por lo tanto ni aparezca en la tabla....

La única condición que controlo yo por programación es que NUNCA puede tener 2 precios promocionales en vigor al mismo tiempo. Al introducir uno, ese rango de fechas se anula y no se puede introducir ningún registro cuyas fechas contenga o este contenida por las fechas ya registradas.


Bien para hacer esto partí de una sentencia INNER JOIN, pero de primeras ya me di cuenta que no funcionaria, dado que en la segunda columna pudiera ser que hubiera un registro o pudiera ser que no. Asi que cambie a una sentencia LEFT JOIN quedandome de esta manera (cuando busco los productos necesito filtrarlos por categoria):

Código MySQL:

SELECT * FROM productos 
LEFT JOIN preciopromocional ON productos.id=preciopromocional.idproducto 
WHERE productos.categoria = 1
 

Esto me devuelve todos los registros de productos cuya categoria sea igual a 1. Aquellos registros que no tuvieran relación en la segunda tabla (es decir, que no hubiera ningun registro con idproducto = id) aparece toda la segunda tabla como NULL, por lo tanto es perfecto, a la hora de decidir el precio compruebo si precio promocional es distinto de NULL, si lo es muestro el normal y si no lo es muestro el promocional. El problema viene en aquellos registros que si tienen presencia en la segunda tabla, y concretamente aquellos que tienen más de una. Por que los repite, como es logico.
Recorre la tabla y encuentra en la segunda tabla varios registros que cumplen la condicón, por lo tanto devuelve una linea por cada registro.

Como comentaba antes, la memoria me es limitada, por lo tanto me interesaria que SOLO devolviera la linea que corresponda, es decir. Si existe algun registro el cual la fecha de hoy este comprendida entre la de inicio y fin, devuelve eso, sino, devuelve todo nulls como si no hubiera encontrado nada.

He ido dando tumbos obteniendo distintos resultados intentando obtener ese return, pero no soy capaz, por ejemplo intente...:

Código MySQL:

SELECT * FROM productos 
LEFT JOIN preciopromocional ON productos.id=preciopromocional.idproducto 
WHERE productos.categoria = 1 AND (preciopromocional.comienzo < '2015-02-02' AND preciopromocional.fin > '2015-02-02')
 

Pero con eso consigo que solo me devuelva aquellos valores que tienen precio promocional en vigor e ignore todos los demás...

Me interesa mucho conseguir esto en una sola sentencia asi que agradezco toda la ayuda posible.

Lamento que el post sea tan largo pero preferia ilustrarlo bien para que resultara mas comodo. Si alguien tiene alguna pregunta adicional por favor no dudeis en ponerla...

Un saludo, y gracias por adelantado!

[gnzsoloyo]

Si existe algun registro el cual la fecha de hoy este comprendida entre la de inicio y fin, devuelve eso, sino, devuelve todo nulls como si no hubiera encontrado nada.

Código MySQL:

SELECT * FROM productos P
LEFT JOIN preciopromocional PP ON P.id = PP.idproducto 
WHERE 
    P.categoria = 1  AND 
    (CURDATE() BEWEEN PP.comienzo AND PP.fin 
    OR PP.idproducto IS NULL);
 

[barbel]

Iniciado por gnzsoloyo

Código MySQL:

SELECT * FROM productos P
LEFT JOIN preciopromocional PP ON P.id = PP.idproducto 
WHERE 
    P.categoria = 1  AND 
    (CURDATE() BEWEEN PP.comienzo AND PP.fin 
    OR PP.idproducto IS NULL);
 

No me sirve. Esa sentencia habia llegado yo, pero falla en aquellos registros que EXISTEN en preciopromocional pero no estan activos actualmente, por ejemplo, en las tablas de arriba el registro con idproducto 5, no lo devolveria en cualquier caso, dado que no se cumplen ninguna de las 2 condiciones (CURRDATE no se encuentra entre las fechas actuales, y idproducto 5 no es nulo, ya que existen 2 registros que satisfacen eso...).

Por lo tanto al recuperar los productos, el producto 5 no me lo devuelve.

Tambien hice ya ayer la evolución logica de esta sentencia que es añadir la posibilidad de que existan y se encuentren fuera de fechas,

Código MySQL:

SELECT * FROM productos P
LEFT JOIN preciopromocional PP ON P.id = PP.idproducto 
WHERE 
    P.idcategoria = 1  AND 
    (CURDATE() BETWEEN PP.comienzo AND PP.fin 
    OR 
    (PP.idproducto IS NULL OR CURDATE() NOT BETWEEN PP.comienzo AND PP.fin))
 

pero en en ese caso, devuelve TODO. Tanto los que estan en periodo promocional como los que no, si un producto tiene 5 registros en preciopromocional, de 3 promociones pasadas, una presente y otra futura, devuelve ese producto 5 veces, una para cada preciopromocional, por lo que no me sirve.
Lo que necesitaria es que si no se cumple la condición de currdate() between inicio y fin, devuelva solo la tabla productos, sin coger nada de la tabla preciopromocional para ese porducto(que por lo tanto a mi me llegaria como NULLS).

Gracias de todas formas, pero me parece a mi que lo que pido es imposible :_)

[gnzsoloyo]

Vovlamos a esto que mencionas:

Por lo tanto nos encontramos en esta situación, en la primera tabla tenemos los productos, y en la segunda los precios promocionales vinculados a esos productos. Puede ser que tenga 1 precio promocional y que este en vigor (la fecha actual este comprendida entre la fecha de inicio y fin), que tenga varios precios promocionales (aunque solo 1 en vigor), que no tenga ningun precio promocional y que por lo tanto ni aparezca en la tabla....

La única condición que controlo yo por programación es que NUNCA puede tener 2 precios promocionales en vigor al mismo tiempo. Al introducir uno, ese rango de fechas se anula y no se puede introducir ningún registro cuyas fechas contenga o este contenida por las fechas ya registradas.

Según se entiende entonces:

1. Todo producto tiene un precio base, ese precio se encuentra en la tabla de Productos.
2. Cada producto puede tener cero (0) o muchos precios promocionales.
3. Cada precio promocional sólo puede tener vigencia en un determinado período.
4. No existen solapamientos de periodos en los precios promocionales.
5. Necesitas que la consulta te devuelva el precio promocional vigente, o bien el precio base si no existe, para una fecha dada...

Si eso es correcto, la consulta tiene dos limitantes:

• No puedes invocar la tabla en forma genérica, sino que debes indicar cada campo en forma específica.
• El precio a obtener requiere la aplicación de funciones.

Código MySQL:

SELECT P.id ir_producto, 
  P.nombre, 
  IFNULL(PP.preciopromocional, P.precio) PrecioLista, 
  P.iva, 
  P.descripcion, 
  P.activo
FROM productos P
LEFT JOIN preciopromocional PP ON P.id = PP.idproducto 
WHERE 
    P.categoria = 1  AND 
    (CURDATE() BEWEEN PP.comienzo AND PP.fin 
    OR PP.idproducto IS NULL);
 

[barbel]

Iniciado por gnzsoloyo

Vovlamos a esto que mencionas:

Según se entiende entonces:

1. Todo producto tiene un precio base, ese precio se encuentra en la tabla de Productos.
2. Cada producto puede tener cero (0) o muchos precios promocionales.
3. Cada precio promocional sólo puede tener vigencia en un determinado período.
4. No existen solapamientos de periodos en los precios promocionales.
5. Necesitas que la consulta te devuelva el precio promocional vigente, o bien el precio base si no existe, para una fecha dada...

Si eso es correcto, la consulta tiene dos limitantes:

• No puedes invocar la tabla en forma genérica, sino que debes indicar cada campo en forma específica.
• El precio a obtener requiere la aplicación de funciones.

Código MySQL:

SELECT P.id ir_producto, 
  P.nombre, 
  IFNULL(PP.preciopromocional, P.precio) PrecioLista, 
  P.iva, 
  P.descripcion, 
  P.activo
FROM productos P
LEFT JOIN preciopromocional PP ON P.id = PP.idproducto 
WHERE 
    P.categoria = 1  AND 
    (CURDATE() BEWEEN PP.comienzo AND PP.fin 
    OR PP.idproducto IS NULL);
 

Tampoco me sirve, aunque es cierto, que la sentencia me ordena las cosas de manera mucho mejor me falla en el momento que un producto tiene un preciopromocional no activo. Es decir, (por ejemplo) el producto 5, tiene un registro en preciopromocional que comienza la semana que viene y termina dentro de 2 semanas. En ese caso no se cumplen las condiciones del WHERE ya que CURDATE() no esta entre comienzo y fin, y idproducto no es nulo (el registro existe solo que no esta en vigor). Asi que vuelve a pasarme lo mismo, al ejecutar esta sentencia me devuelve todos los productos que no tienen registro en preciopromocional, me devuelve todos los productos que tiene un preciopromocional activo actualmente (todo eso de manera perfecta), pero no me devuelve los productos que tienen un preciopromocional NO ACTIVO actualmente.

De nuevo otra vez, muchas gracias....

jamas pensé que podría complicarse tanto una sentencia

[gnzsoloyo]

En ese caso, por simplicidad de código, deberás crear una consulta en dos partes con UNION, una para cada condición.
¿No te parece medio evidente?
A fin de cuentas, la condición de Activo/No Activo es mandatoria en la consulta, por lo que resulta incompatible con la sintaxis actual.