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

Filed under:
|

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

Related posts about mysql-query

Related posts about left-join