Mysql Select, two tables.

Hi!

I need help to do this thing, if possible in SQL

Table 1
ID | xpto | Cliente
----------------------------
1 | rerere | 12345

Table 2
ID | Cliente | c1 | c2 | date
--------------------------------------
1 | 12345 | abcd | abcd | 2006-08-30
2 | 12345 | abcdef | abgh | 2006-09-01

so i want the table 1 with the last row from table 2 (i.e. with the
most recente date)
Resulta table
t1.ID | t1.xpto | t1.Cliente | t2.ID | t2.Cliente | t2.c1 | t2.date
--------------------------------------------------------------------------------------
1 | rerere | 12345 | 2 | 12345 | abgh | 2006-09-01

Now imagine this to many rows. I only want info regard the cliente from
table 2 that is the moste recent.

Can you help me?

Thanks in advance Pedro Coelho

Pedro Coelho wrote:
> Hi!
>
> I need help to do this thing, if possible in SQL
>
> Table 1
> ID | xpto | Cliente
> ----------------------------
> 1 | rerere | 12345
>
> Table 2
> ID | Cliente | c1 | c2 | date
> --------------------------------------
> 1 | 12345 | abcd | abcd | 2006-08-30
> 2 | 12345 | abcdef | abgh | 2006-09-01
>
> so i want the table 1 with the last row from table 2 (i.e. with the
> most recente date)
> Resulta table
> t1.ID | t1.xpto | t1.Cliente | t2.ID | t2.Cliente | t2.c1 | t2.date
> --------------------------------------------------------------------------------------
> 1 | rerere | 12345 | 2 | 12345 | abgh | 2006-09-01
>
> Now imagine this to many rows. I only want info regard the cliente from
> table 2 that is the moste recent.
>
> Can you help me?
>
> Thanks in advance Pedro Coelho

Untested (sometimes I get a & b mixed up!):

SELECT t1.ID,t1.xpto,t1.Cliente,t2.ID,t2.Cliente,t2.c1,t2 .date
FROM
(SELECT table2.*
FROM table2 a
LEFT JOIN table2 b ON a.Cliente = b.Cliente
AND a.ID <> b.ID
AND a.date < b.date
WHERE b.date IS NULL) t2
LEFT JOIN table1 t1
ON t1.Cliente = t2.Cliente;

> Table 1
> ID | xpto | Cliente
> ----------------------------
> 1 | rerere | 12345
>
> Table 2
> ID | Cliente | c1 | c2 | date
> --------------------------------------
> 1 | 12345 | abcd | abcd | 2006-08-30
> 2 | 12345 | abcdef | abgh | 2006-09-01
>
> so i want the table 1 with the last row from table 2 (i.e. with the
> most recente date)
> Resulta table
> t1.ID | t1.xpto | t1.Cliente | t2.ID | t2.Cliente | t2.c1 | t2.date
> --------------------------------------------------------------------------------------
> 1 | rerere | 12345 | 2 | 12345 | abgh | 2006-09-01
>
> Now imagine this to many rows. I only want info regard the cliente from
> table 2 that is the moste recent.

select table1.* , table2.* from table1,table2
where table1.Cliente=table2.Cliente
and table2.date = (select max(date) from table2);


Regards
Dimitre

Thanks that worked with a little change

SELECT * FROM
(SELECT a.*
FROM clientes a
LEFT JOIN clientes b ON (a.Cliente = b.Cliente
AND a.ID_Cliente <> b.ID_Cliente
AND a.Data < b.Data)
WHERE b.Data IS NULL) t2
LEFT JOIN taxas_negociadas t1
ON t1.Cliente = t2.Cliente;

there was a little "(" on the first join that was forgoted.

strawberry escreveu:
>
> Untested (sometimes I get a & b mixed up!):
>
> SELECT t1.ID,t1.xpto,t1.Cliente,t2.ID,t2.Cliente,t2.c1,t2 .date
> FROM
> (SELECT table2.*
> FROM table2 a
> LEFT JOIN table2 b ON a.Cliente = b.Cliente
> AND a.ID <> b.ID
> AND a.date < b.date
> WHERE b.date IS NULL) t2
> LEFT JOIN table1 t1
> ON t1.Cliente = t2.Cliente;

Hi!

That almost worked, but only if all the clientes for intersect with the
first table as an iqual date.

It was simple, but did not do the job.

Thanks.

Radoulov, Dimitre escreveu:
> > Table 1
> > ID | xpto | Cliente
> > ----------------------------
> > 1 | rerere | 12345
> >
> > Table 2
> > ID | Cliente | c1 | c2 | date
> > --------------------------------------
> > 1 | 12345 | abcd | abcd | 2006-08-30
> > 2 | 12345 | abcdef | abgh | 2006-09-01
> >
> > so i want the table 1 with the last row from table 2 (i.e. with the
> > most recente date)
> > Resulta table
> > t1.ID | t1.xpto | t1.Cliente | t2.ID | t2.Cliente | t2.c1 | t2.date
> > --------------------------------------------------------------------------------------
> > 1 | rerere | 12345 | 2 | 12345 | abgh | 2006-09-01
> >
> > Now imagine this to many rows. I only want info regard the cliente from
> > table 2 that is the moste recent.
>
> select table1.* , table2.* from table1,table2
> where table1.Cliente=table2.Cliente
> and table2.date = (select max(date) from table2);
>
>
> Regards
> Dimitre