[danielf]

I need help with an SQL statement. I would really appreciate it if someone could help me.

I have 3 tables.

users
- userid, int pk (auto)
- username, varchar username (ex. foobar)

events
- eventid, int pk (auto)
- description event description (ex. BIG PARTY!)

confirm
- confirmid, int pk (auto)
- eventid, int fk
- userid, int fk
- confirm, int 0 if user is not attending, 1 if user is attending, 2 if user is not sure
- timestamp (auto)

System works the following way.

A user creates a new event (party, meeting, etc) [table events].
Users confirm if they will attend, will not or isnt sure [table confirm].
Users can change their mind.
So if a user confirms he will attend and then changes his mind, table confirm will have 2 entries with his userid. One entry

will have confirm=1 and the other will have confirm=0 or confirm=2.

I need and sql statement that will return me the following information for a giver userid:

events.eventid, events.description, confirm

"confirm" column should be:
- NULL if no rows are found in table confirm with the users given userid
- 0 if last entry for that user was a 0
- 1 if last entry for that user was a 1
- 1 if last entry for that user was a 2

An example would be

events.eventid events.description confirm
1 BIG PARTY! NULL
2 Staff Meeting 0
3 Paintball 1
4 Sking 1
5 Poker 2

User's last confirmation is that he is going Sking and playing Paintball. He will not attend the Meeting.
He has not decided if he is going to the party or not. He has not decided if he is going to play poker.