MySQL Query question (outer joins w/conditions)

[hendricksonet]

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!

 

[kirk112]

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

[hendricksonet]

Gosh that's a lot smaller of a query then I've been trying, I hope it works! hehe