o1548324 (10/11/2012)
I tried your query but its not returning the right results, it seems like it is stopping after it hits the first records that meets the query conditions because the very first result is a member who had an active membership but it was the second enrollment segment when you query her ID in the table.
That's because I don't have anything to test with.
With the sample data you posted, it should only return the row with the football.
SQL Server won't "stop" after the first record that meets the conditions. It will bring all the set of rows that meet the conditions.