Struggling with correlated subqueries in SQL

Question

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?

Usually you don't have correlated subqueries when doing IN. (But when you do EXISTS you have correlated subqueries.) — jarlh
– jarlh, Commented May 28, 2019 at 9:59
Join/group by would work fine. Join all your tables and then count the distinct event ids whilst grouping by member id. What was the problem you were having with the join approach? — Charleh
– Charleh, Commented May 28, 2019 at 10:01
Seems an odd design where a car can attend an event rather than a member and what if 2 members have shares in one car? — P.Salmon
– P.Salmon, Commented May 28, 2019 at 10:03
@Charleh join/group by did the trick. When i performed it i must have forgotten the distinct i believe, but that was before — kyllion001
– kyllion001, Commented May 28, 2019 at 10:30

slaakso · Accepted Answer · 2019-05-28 10:09:37Z

3

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

answered May 28, 2019 at 10:09

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kyllion001 Over a year ago

Thanks alot your code works perfectly, i should have stuck with the group by instead of trying out subqueries.

slaakso Over a year ago

You always need to use GROUP BY when you mix normal columns with aggregate functions. Nothing to to with the subqueries. In your query you have m.memberId as a normal column and count(a.eventId) as an aggregate function. This would mean that you would have to use group by m.memberId (disregarding other errors in your query).

Alistair Boynton · Accepted Answer · 2019-05-28 10:08:08Z

0

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.

answered May 28, 2019 at 10:08

Alistair Boynton

11 bronze badge

2 Comments

kyllion001 Over a year ago

You're right about the d.CarId, i translated this from dutch and must have missed this one. Is a group by mandatory to get results such as this?

Charleh Over a year ago

Group by is mandatory in order to aggregate assuming you aren't just using the aggregate column on its own (if you need only the aggregate value and no other columns you don't need to group in SQL Server since it will assume you just wanted to aggregate over the whole set and return 1 result)

Collectives™ on Stack Overflow

Struggling with correlated subqueries in SQL

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related