Help with SQL statement

[Hysteria86]

Hi,

I've got a program that represents the booking of groups for evening education classes. There are two tables, group_members (all the members in a particular group) and scheduled_event_members (the members who are actually attending a particular event).

I've programmed it so that whenever a new member is added/removed from a group, the list of members in the scheduled event is automatically updated.

I can get the list of members for each event, and it updates whenever a new member is added or one is removed, however it brings back all the members in a group, not just the ones booked into the scheduled event, which is what I want.

Bear with me,

How can I say:

"GET all group_members, WHERE group_members.customer_id IS NOT EQUAL TO scheduled_event_members.customer_id (whose scheduled_event_members record is set to Deleted)"


SELECT
*
FROM
scheduled_event AS se,
scheduled_event_members AS sem,
group_members,
WHERE
se.scheduled_event_id = ".$_REQUEST['intScheduleID']."
AND
groups_members.group_id = ".functions::sanitiseIn($_REQUEST['intGroupId'])."
AND
se.se_id = sem.se_id
AND
se.deleted != 'Y'
AND
sem.deleted != 'Y'
AND
groups_members.customer_id != sem.customer_id
AND
groups_members.deleted != 'Y'
GROUP BY
group_members.customer_id




Thanks

[themarty]

Are you sure this is what you want:

"GET all group_members, WHERE group_members.customer_id IS NOT EQUAL TO scheduled_event_members.customer_id

Suppose you have 4 members (ids:1,2,3,4) and all all four are in the sem table. When you join these two table using "WHERE group_members.customer_id IS NOT EQUAL TO scheduled_event_members.customer_id", then member 1 would be joined to customer_id 2,3&4, member 2 with 1,3,4 etc.

I guess what you want is all the members that participate in a scheduled event?

SELECT
*
FROM group_members AS gm, scheduled_event AS se, scheduled_event_members AS sem,
WHERE gm.customer_id=sem.customer_id /* this selects all the members who particpate in any event (deleted or not)
AND se.se_id = sem.se_id /* this joins the event-data */
AND gm.group_id = [some-group-id] /* this selects only members from a certain group */
AND se.scheduled_event_id = [some-event-id] /* this select a particular event */
AND se.deleted != 'Y' AND sem.deleted != 'Y' /* this filters out any deleted data */