Beginner's Question: How To Write This Query

Hi -

I feel stupied b/c I can't figure this out but I hope it's just a few seconds for some of the more senior posters of this forum:

I have a table that looks like this:

Table:Request
ID: bigint
... (a bunch of table specific columns)
User_Status_ID: bigint (holds the bigint ID of the status table, below; always holds the ID to the most recent ID, if several)
Provider_Status_ID: bigint (holds the bigint ID of the status table, below;  always holds the ID to the most recent ID, if several)

and then there is a second table that looks like this:

Table: Status
ID: bigint
RequestID: bigint (the request ID, so I can see all status messages for the request)
Time: Timestamp
Code: byte (there are only a few status messages)

The status table holds both the status for users and providers, as they use exactly the same status code. What I'd like to get is a table/view that looks like this:

RequestID:bigint (the request that this status belongs to)
User_Code: byte (the code of th most recent user status)
Provider_Code: byte (the code of the most recent provider status)

Somehow, I am blanking how to create a query that returns both user and provider codes in one row. Any help greatly appreciated!!!!

 

Oliver