Multiple joins (only need last entry from 1)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple joins (only need last entry from 1)

Author

Topic

rodb_hi
Starting Member

1 Post

Posted - 2009-06-15 : 17:32:03

I am trying to join 3 tables concerning expense reports

Table 1 expense_report

eID Seq Report Name
--- --- --------------
100 1 Expense Report 1
100 2 Expense Report 2

Table 2 expense_detail

eID Seq item id eDate eAmount
----- --- ------- ----- ---------
100 1 1 1/1/09 100.00
100 1 2 1/3/09 150.00
100 2 1 2/1/09 50.00
100 2 2 2/5/09 75.00

Table 3 expense_status_history (History status of the report)

eID seq item ID Status Status Date
--- ---- ------ ---------- -------------
100 1 1 New 1/1/09
100 1 1 reviewed 1/10/09
100 1 1 approved 1/12/09
100 1 2 new 1/2/09
100 1 2 approved 1/3/09
100 2 1 New 2/5/09

I want to write a query that will extract the following

eid, report name, item id, amount, status (Only the last status by date)

select * from expense_detail JOIN expense_report
ON expense_detail.seq = expense_report.seq and expense_detail.eID = expense_report.eID;

This seems to work fine but when I put in the 3 join I get all the statuses back for each detial and I only what the last status.

I tried to write a Select Max and a TOP 1 but I am not sure how to do it with mulitple slection criteria.

thanks for you help

singularity
Posting Yak Master

153 Posts

Posted - 2009-06-15 : 19:32:07

[code]
select a.eID, a.report_name, b.item_id, b.eamount, c.status
from expense_report a
join expense_detail b on a.eID = b.eID
join (select y.eID, y.status
from expense_status_history y
join (select eID, max(status_date) as max_status_date
from expense_status_history
group by eID) z on y.eID = z.eID and
y.status_date = z.max_status_date) c on a.eID = c.eID [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources