hendricksonet Posted October 3, 2007 Share Posted October 3, 2007 I'm going out on a whim here to see if anyone can give some insight on how to do this. I'm sure I'm just overthinking it.... Here are my tables (events, notAttending) events e --------------- | event ID | ... | --------------- | 1 | ... | | 2 | ... | | 3 | ... | | 4 | ... | ---------------- notAttending n ------------------------------------------- | notID | eventID | memberID | subMemberID | ------------------------------------------- | 1 | 1 | 4 | null | | 2 | 1 | 2 | null | | 3 | 1 | 4 | 2 | | 4 | 3 | 1 | 3 | | 5 | 4 | 1 | null | ------------------------------------------- What I want is a list all the events and the count of the number of times that eventID is displayed in the notAttending table when subMemberID is null. So the result would be this: ---------------------- | count | event ID | ... | |---------------------- | 2 | 1 | ... | <-- Event ID 1 shows up in notAttending table three times, but only twice with a subMemberID of null | 0 | 2 | ... | <-- Event ID 2 doesn't show up in notAttending table | 0 | 3 | ... | <-- Event ID 3 is in the notAttendingTable, but subMember is not null so it doesn't add to the count | 1 | 4 | ... | <-- Event ID 4 shows up once in notAttending table with a subMemberID of null ---------------------- I've tried doing some outer joins, unions, etc but every time I do that it doesn't give me all the event ids or it gives me the event ID's, but counts even when the subMemberID <> null. Any ideas would be much appreciated Thanks! Link to comment https://forums.phpfreaks.com/topic/71717-mysql-query-question-outer-joins-wconditions/ Share on other sites More sharing options...
kirk112 Posted October 3, 2007 Share Posted October 3, 2007 Not tested this but this might work? SELECT event_id, SUM( IF( subMemberID IS NULL, 1, 0 ) ) FROM EVENTS AS e LEFT JOIN notAttending AS n ON e.event_id = n.eventID GROUP BY event_id Let us know how you get on Link to comment https://forums.phpfreaks.com/topic/71717-mysql-query-question-outer-joins-wconditions/#findComment-361176 Share on other sites More sharing options...
hendricksonet Posted October 3, 2007 Author Share Posted October 3, 2007 Gosh that's a lot smaller of a query then I've been trying, I hope it works! hehe Link to comment https://forums.phpfreaks.com/topic/71717-mysql-query-question-outer-joins-wconditions/#findComment-361196 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.