Hi Guys,

I have a table called helpdesk (ID(PK), Description,Category,CreatedDate)

Each helpdesk record can have many statuses. So a user opens a request. The technician then add's status updates as work progresses. Examples of status updates are Open, Work In progress,Complete, Closed. I store those in a table called helpdeskstatustype.

Since a request can have multiple statuses I store the status records in a table called helpdeskstatus (id, helpdeskid(FK to helpdesk),statusid(FK to helpdeskstatustype table), statusdate,createdby)

Now I need to write a query that lists each helpdesk item along with the latest status.

Example of helpdesk data:

1 This is a test Hardware, 2012-06-15

example of statustype data:

1 Open
2 Work in progress
3 Closed

example of heldeskstatus data
1 1 1 2012-06-15 3
2 1 2 2012-06-17 5
3 1 3 2012-06-17 3

so my query must display each field from the helpdesk table as well as the last update status above which will be "closed". Please help