• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Need help with SQL statement - aggregate function in joined table

I have two tables:
Table1 with columns:
ID
Date1
Date2

Table2 with columns
ID
Date3

There is a 1->many relationship between table 1 and table 2

I need a query that looks like this:
ID   Date1   Date2  Max(Date3)

So far I haven't quite gotten this to work:

select a.loan, a.date1, a.date2, b.date3
from
table1 a, (select loan, max(date3) from
(select * from table2 c where a.loan = c.loan) ) b
where a.loan = b.loan
0
WhidbeyIslndr
Asked:
WhidbeyIslndr
2 Solutions
 
aaaaaaCommented:
select a.loan, a.date1, a.date2, max(b.date3)
from table1 a, table2 b
where a.loan = b.loan
group by a.loan, a.date1, a.date2
0
 
Renante EnteraCommented:
Hi WhidbeyIslndr!

You can also do it this way :

Select a.Loan, a.Date1, a.Date2, Max(b.Date3)
From table1 a
Inner Join table2 b
  On b.Loan = a.Loan
Group by a.Loan, a.Date1, a.Date2

* Note : If you need to return those records without max(Date3) then use "Left Join" instead of "Inner Join".

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now