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! Quote 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 Quote 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 Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.