How to solve cartesian product
625986Jan 30 2009 — edited Jan 30 2009Hi, I have 2 tables ('production' and 'dates'), I need to join the 2 tables without the production value of the production table is repeated in other days.
In my example, I have 500 pieces of product 1000 which was produced in 01/04/2009
Example:
select a.cod_company, a.cod_item, a.dat_prod, a.qt_prod, b.date
from the production a, dates b
left join dates on a.dat_prod = b.date
where a.cod_comp = 1 and a.cod_item = 1000
and b.date between '01/01/2009' and '01/31/2009'
This returns:
1..1000...01/01/2009...500
1..1000...01/02/2009...500
1..1000...01/03/2009...500
1..1000...01/04/2009...500
.
.
.
1..1000...01/31/2009...500
When should bring:
1..1000...01/01/2009...0
1..1000...01/02/2009...0
1..1000...01/03/2009...0
1..1000...01/04/2009...500
.
.
.
1..1000...01/31/2009...0