Sorry the title is very vague.
Basically, I'm stuck with something. I have an ample working script which lists from two tables.
It pulls a list of events
Event 1
Event 2
Event 3
Event 4
Event 5
Event 6
Now, users need t-shirts for the "overall" event. They are volunteers, and they have t-shirts. When they submit which events that can help steward, they choose their t-shirt size so I can order them.
I then get a list
Event 1:
Small = Count
Medium = Count
Large = Count
When the volunteer enters their t-shirt size, it is saved in their PROFILE. The events are stored in a table called events.
However, that part isn't important. I can easily get it to list as above and it does count correct, to a certain extent. The problem is, say I have a volunteer called Bob. Bob's t-shirt size is Small. He is only volunteering for Event 1. NO OTHER VOLUNTEERS HAVE ENTERED THE DAYS THEY CAN HELP YET. So I would have all events listed, and the only area that would equal anything other than 0 is
Event 1 - Small = 1, Medium = 0, Large = 0 Event 2 - Small = 0, Medium = 0, Large = 0 etc. etc....
Anyway, say Bob decides to help at Event 1,2,3 and 5.
It is then showing
Event 1 - Small = 1, Medium = 0, Large = 0
Event 2 - Small = 1, Medium = 0, Large = 0
Event 3 - Small = 1, Medium = 0, Large = 0
Event 4 - Small = 0, Medium = 0, Large = 0
Event 5 - Small = 1, Medium = 0, Large = 0
Event 6 - Small = 0, Medium = 0, Large = 0
It's showing that because the query is basically
SELECT u.id, u.userid, u.eventid, u.helping, i.userid, i.tshirt_size FROM helpers AS u LEFT JOIN profiles AS i ON u.userid=i.userid WHERE `eventid`='$eventid' AND u.helping='1' AND i.tshirt_size='$size'
I then echo that with a function, like getTShirtCount("eventid","Small");
Event ID is picked up by a MySQL Fetch Array loop, looping the Events I have in my database (which is also where the event ID is picked up from).
Event 1 = ID: 1
Event 2 = ID: 2
etc..
When they submit the events they can help, it goes into a "helpers" table, like
UserID = 101
EventID = 1
Helping = 1
UserID = 101
EventID = 2
Helping = 1
UserID = 101
EventID = 3
Helping = 1
UserID = 101
EventID = 4
Helping = 0
etc...
== WHAT I WANT TO DO ==
IF the user, Bob, has been listed in Event 1 - I don't need him to be counted in ANY OTHER EVENT. The count is so that I can bring the correct amount of t-shirts to each event to distribute. So it may tell me I need 50 small, 10 large, 40 medium.
If Bob is attending Event 1, he will get his t-shirt then, so he doesn't need me to bring another (thus be counted) in any other event.
So, I am essentially needing it to ONLY add 1 to the num_rows count, IF "Bob" has no "Helping='1'" for any previous events.
I hope I've explained that well enough.
Can anyone help?
I had to assume your events table has fields eventID and event_name, I could guess other tables from the query in your question.
SELECT e.event_name,
SUM( eh.shirt_size <=> 'Small' ) as Small,
SUM( eh.shirt_size <=> 'Medium' ) as Medium,
SUM( eh.shirt_size <=> 'Large' ) as Large
FROM events e
LEFT JOIN
( SELECT h.userID, MAX(p.tshirt_size) as shirt_size, MIN(eventID) as first_event
FROM helpers h
JOIN profiles p ON p.userID = h.userID
WHERE h.helping = 1
GROUP BY h.userID ) eh
ON e.eventID = eh.first_event
GROUP BY e.event_name
A working example: http://sqlfiddle.com/#!2/33f9b/1