Hi all.
I need your help with the query or table restructure.
Basicly I have 3 tables:
-users that contains user data (userId, ...).
-eventDesriptions contains some events a user can do (eventId, eventDescription).
-events: i write here when user makes a event. It contains 3 fields - userId, eventId, date.
So, my events table looks like that:
userId eventId date
1 1 2008/11/22
2 1 2008/11/22
1 2 2008/11/22
...
This means user1 comleted events 1 and 2, user2 completed event 1. There will be no row with userId = 1 and eventId = 7 if user1 hasn't completed event 7....
I can easily see which events user1 made, i can also easily get users that have done events 1, 2 and 7
But what I'd like to get is the list of users that have completed event 1 and haven't completed event 2.
How can I achieve that? I tried transform function, but it takes too long when tables grows, I've also tried joining users with events to get all possible combinations, but it seems to take too much time too.
I expect at least 20000 records in users table and have 30 different events for now.
Do you have any better idea to perform this query?
Or you suggest a different table structure?
Thanks
I need your help with the query or table restructure.
Basicly I have 3 tables:
-users that contains user data (userId, ...).
-eventDesriptions contains some events a user can do (eventId, eventDescription).
-events: i write here when user makes a event. It contains 3 fields - userId, eventId, date.
So, my events table looks like that:
userId eventId date
1 1 2008/11/22
2 1 2008/11/22
1 2 2008/11/22
...
This means user1 comleted events 1 and 2, user2 completed event 1. There will be no row with userId = 1 and eventId = 7 if user1 hasn't completed event 7....
I can easily see which events user1 made, i can also easily get users that have done events 1, 2 and 7
But what I'd like to get is the list of users that have completed event 1 and haven't completed event 2.
How can I achieve that? I tried transform function, but it takes too long when tables grows, I've also tried joining users with events to get all possible combinations, but it seems to take too much time too.
I expect at least 20000 records in users table and have 30 different events for now.
Do you have any better idea to perform this query?
Or you suggest a different table structure?
Thanks