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