Query help needed (JOIN)

I have two tables populated during the use of an application to log user events and application states. They are named "EventTable" and "StateTable" and the structures follow:

EventTable:

ID EventTimeStep EventID
-- ------------- ---------

1  5              E1
2  22             E2
3  56             E3

StateTable

ID StateTimeStep StateID
-- ------------- -------

1  1              S1
2  39             S2

I want to perform a query that reports the StateID of the application at the time that each event was logged to the EventTable. The desired output is:

ID TimeStep EventID StateID
-- -------- ------- -------

1  5        E1      S1
2  22       E2      S1
3  56       E3      S2

I have tried to create a query with an INNER JOIN where the value for the StateID output field comes from the last row in the StateTable WHERE StateTable.TimeStep <= EventTable.TimeStep and where I use a GROUP_BY EventTable.ID to merge the following rows from the join:

3 56 E3 S1
3 56 E3 S2

However, the closest I can get is a query that gives me the wrong state when applying the GROUP BY clause

3 56 E3 S1

I also think that the queries I have written is slow and inefficient. Is there a better way to perform this query or is my database design fatally flawed?

Thanks,

Adam Nemitoff Received on Wed Aug 25 2004 - 08:18:57 CDT