Jump to content

MySQL Query question (outer joins w/conditions)


hendricksonet

Recommended Posts

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!

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.