I am trying to build an SQL statement that will pull data from a parent table and count rows in a child table.
Parent table - event:
eventid, title 1 'Metallica is coming' 2 'Elvis is in town'
Child table - eventattendance:
eventid, userid, response 1 24 Yes 1 5 Yes 2 7 Maybe 1 7 No 2 18 No 2 6 Yes
As a result of my query I am trying to have one line per event that would list attendance count for that event like this:
eventid, attendYes, attendMaybe, attendNo 1 2 0 1 2 1 1 1
So this is the query I am running:
SELECT eventTable.eventid AS eventID, COUNT(attendanceYes.response = 'Yes') AS attendYes, COUNT(attendanceNo.response = 'No') AS attendNo, COUNT(attendanceMaybe.response = 'Maybe') AS attendMaybe FROM event AS eventTable LEFT JOIN eventattendance AS attendanceYes ON ( attendanceYes.eventid = eventTable.eventid AND attendanceYes.response = 'Yes' ) LEFT JOIN eventattendance AS attendanceNo ON ( attendanceNo.eventid = eventTable.eventid AND attendanceNo.response = 'No' ) LEFT JOIN " . TABLE_PREFIX . "eventattendance AS attendanceMaybe ON ( attendanceMaybe.eventid = eventTable.eventid AND attendanceMaybe.response = 'Maybe' ) WHERE date > $today GROUP BY eventTable.eventid LIMIT 10
This query does not give me the desired result. It looks like it counts the 'Yes' responses twice and places the number into both attendYes and attendNo. Can anyone suggest the proper way to do it?