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!