CODEWITHSUNDEEP
mysqlsqlsubquery
kyllion001
kyllion001

Reputation: 25

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 :

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?

Upvotes: 0

Views: 89

Answers (2)

slaakso
slaakso

Reputation: 9080

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

Upvotes: 3

Alistair Boynton
Alistair Boynton

Reputation: 1

Not sure if you are using that exact statement, but your syntax has several issues. You're using aliases that don't exist (d.CarID - what table is d?) and you don't have a group by statement at the end, which tells the engine what columns you want to preserve the values from. Try something like this:

select member_ID,
  count(*)
from (
  select distinct a.eventID, m.memberID
  from attendants a
  inner join cars c
  on a.carID = c.car_ID
  inner join members m
  on c.memberID = m.memberID
  group by a.eventID, m.memberID
)
group by memberID

The inner query gets what you want - a big list of all the members who have attended via the cars they own, deduped so that if they had two cars at the same event they are only recorded once - which is then counted for each member.

Upvotes: 0

Related Questions