samedi 25 avril 2015

SQL query returning events with an odd number of users assigned to them


I have 5 tables--users, groups, events, users_groups_link, users_events_link. Like so:

users
user_id

groups
group_id

events
event_id

users_groups_link
user_id
group_id

users_events_link
user_id
event_id

Each event has 2 or 4 groups assigned to it and each group contains 8-11 users. user_id=1 is a group vacancy. I'm wanting to make sure that each event has an even number of users so I'm trying to create a query that returns all events containing an odd number of users. So far I have this:

SELECT user_id,event_id 
FROM users,groups,events,users_events_link,users_groups_link
WHERE users.user_id=users_events_link.user_id
AND events.event_id=users_events_link.event_id
AND users.user_id=users_groups_link.user_id
AND groups.group_id=users_groups_link.group_id
AND user_id <> 1

which returns all events and users that are assigned to them (via their host group) like so:

user_id         event_id
56              1001
34              1001
76              1001
45              1001
87              1001
88              1001
54              1001
4               1001
63              1002
69              1002
77              1002
etc.

Now I'm wanting to refine this query further by just returning events with an odd number of users assigned to them i.e. events with their id in the right hand column repeating an odd number of times. And then maybe refining it further by eliminating the users_id column entirely and returning one event id for each event containing an odd number of users.

I've tried using "count()" and "group by" but am not getting the desired results so i'm obviously doing something wrong but I don't know what. Thanks in advance.


Aucun commentaire:

Enregistrer un commentaire