Struggling with correlated subqueries in SQL

I have a database about old cars, their owners, events and the attendants of those events as such:

Image of tables used :

PK: Primary Key FK: Foreign Key

Now I need to get the amount of different events (eventId) each member (memberId) has attended. Important note: a member can have multiple cars that can each attend events.

Here is one of my attemps:

select m.memberId, count(a.eventId).
from members m where m.memberId in (select c.memberId from cars c where m.memberId =
    c.memberId and c.carId in
    (select d.carId from attendants a where c.carId = d.carId))
order by m.memberId

I’ve also tried using joins and group by to get a correct result but I am getting nowhere. Does anyone know how i need to form the subquery so that i can get the results needed?

Answer

So you want distinct events the member has attended. Member has cars which attend the events. Since different cars can attend same events, you need to take distinct from events:

select m.memberId, count(distinct a.eventId)
from members m 
  join cars c on c.memberId = m.memberId
  join attendants a on a.carId = c.carId
group by m.memberId

Advertisement

Answer