MySql left join on several regs
Posted
by egidiocs
on Stack Overflow
See other posts from Stack Overflow
or by egidiocs
Published on 2010-04-05T20:59:13Z
Indexed on
2010/04/05
21:03 UTC
Read the original article
Hit count: 313
mysql-query
|left-join
Hi there! I have this table1
idproduct(PK) | date_to_go
1 2010-01-18
2 2010-02-01
3 2010-02-21
4 2010-02-03
and this other table2 that controls date_to_go updates
id | idproduct(FK) | prev_date_to_go | date_to_go | update_date
1 1 2010-01-01 2010-01-05 2009-12-01
2 1 2010-01-05 2010-01-10 2009-12-20
3 1 2010-01-10 2010-01-18 2009-12-20
4 3 2010-01-20 2010-02-03 2010-01-05
So, in this example, for table1.idproduct #1 2010-01-18 is the actual date_to_go and 2010-01-01 (table2.prev_date_to_go, first reg) is the original date_to_go .
using this query
select v.idproduct, v.date_to_go, p.prev_date_to_go original_date_to_go
from table1 v
left join produto_datas p on p.idproduto = v.idproduto
group by (v.idproduto)
order by v.idproduto
can I assume that original_date_to_go will be the first related reg of table2?
idproduct | date_to_go | original_date_to_go
1 2010-01-18 2010-01-01
2 2010-02-01 NULL
3 2010-02-21 2010-01-20
4 2010-02-03 NULL
© Stack Overflow or respective owner