Query help needed (JOIN)

[Adam Nemitoff]

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

[Simon Hayes]

"Adam Nemitoff" <ad***@TeamNemitoff.com> wrote in message
news:15**************************@posting.google.c om...

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

I think this should work (not tested):

select et.EventTimeStep, et.EventID, max(st.StateID)
from EventTable et
join StateTable st
on st.StateTimeStep <= et.EventTimeStep
group by et.EventTimeStep, et.EventID

If this doesn't work as expected, please provide CREATE TABLE statements,
with INSERTs for the data, to avoid any confusion.

As a side remark, it's not clear what the "ID" columns are - EventID and
StateID look like the natural primary keys. Also, my query doesn't return
any "ID" in the output - if you need to number the rows, it's usually best
to do that in the client application:

http://www.aspfaq.com/show.asp?id=2427

Simon

[Hugo Kornelis]

On 25 Aug 2004 06:16:46 -0700, Adam Nemitoff wrote:

(snip)

Hi Adam,

You posted this same question to comp.databases.theory as well. I believe
I saw an answer there - no need to go for doubles.

In the future, please don't multi-post!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)