CODEWITHSUNDEEP
mysqlsql
dguan
dguan

Reputation: 1033

how to select one record from multi records of another table?

I have multiple tables, Table 1:

ID    Name
1     Adam
2     Bob

...

Table 2:

ID    Visit
1     2019-01-01
2     2019-01-02
1     2019-01-03
1     2019-01-04
2     2019-01-04
1     2019-01-05

...

I would like to get the most recent visit from table 2 for each name in Table 1, such as

Name    LastVisit
Adam    2019-01-05
Bob     2019-01-04

I tried join them together using

select tbl1.name as "Name", MAX(tbl2.visit) as LastVisit
from table1 tbl1 join table2 tbl2 on tbl1.id=tbl2.id;

but it is not working, how should I do this?

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You just need a group by:

select t1.name, MAX(t2.visit) as LastVisit
from table1 t1 join
     table2 t2
     on t1.id = t2.id
group by t1.name;

Upvotes: 1

Related Questions