Tengo dos tablas que representan una base de datos para productos de clientes y productos de sus competidores:

tmp_match - from_product_id y to_product_id que representan coincidencias entre el producto del cliente y el producto de la competencia, respectivamente.

tmp_price_history: muestra el precio de cada producto por fecha.

Estoy intentando escribir una consulta que enumerará todas las fechas de la tablatmp_price_history. Para cada fecha, quiero ver el precio del producto del cliente frente al precio del producto de la competencia de acuerdo con los pares de coincidencias de productos en la tablatmp_match, independientemente de si hubo un registro de historial de preciospara productos del cliente o productos de la competencia o ambos:

si ambos precios están disponibles para una fecha específica: enumere ambos en sus columnas

si solo hay un registro para el producto del cliente: muestre solo el precio del cliente (y deje en blanco la columna de la competencia).

si solo hay un registro para el producto de la competencia, muestre el precio de la competencia en su columna.

Resultado Esperado

date    from_product_id to_product_id   cust_price  comp_price
1       1               11              99          95
2       1               11              98          94
1       1               12                          92
2       1               12                          91
2       2                               108                 

Intenté lograr eso usando esta consulta:

 select cust_hist.date, from_product_id, to_product_id, cust_hist.price as cust_price,comp_hist.price as comp_price
from tmp_match as matches
       left join tmp_price_history cust_hist
         on cust_hist.product_id = matches.from_product_id
       left join tmp_price_history comp_hist
         on  comp_hist.product_id = matches.to_product_id
;

pero no alcanza mi objetivo como se puede ver en estesql snippet.