I have the following structure:

• Training Type
  • Contains multiple sessions
    • Each session can have multiple slots
      • A slot has a start date. (date column)

How to compute for each Training Type the total of sessions whose all the slots are later than today ?

What's important here is that I can't use a WHERE statement otherwise it will remove the row training 3.

I tried that but the result is wrong, it counts the total of joined rows on sessions.

I also tried the case method but I'm not sure if the method is not right, or my min(date) function is not behaving as expected

I don't really have an advanced level in SQL so I don't know what exact terms I should look for

Some data:

1. TrainingType

2. Training Sessions

3. Training sessions

Expected output:

CodePudding user response：

Using a subquery:

select t.id, t.name, t.required_age, t.category, (select count(*) 
    from TrainingSessions t1 where t.id = t1.type_id and not exists 
     (select 1 from Training_sessions t2 where t2.training_session_id = t1.id and t2.start <= now()))
from TrainingType t 

See fiddle.

CodePudding user response：

You could use aggregation and left join as the following:

select TT.id, TT.name, TT.required_age, TT.category, count(D.type_id)
from TrainingType TT left join
(
  select T1.training_session_id, T2.type_id
  from TrainingSessionsSlots T1 join TrainingSessions T2
  on T1.training_session_id = T2.id
  group by T1.training_session_id, T2.type_id
  having min(start) > now()
) D
on TT.id = D.type_id
group by TT.id, TT.name, TT.required_age, TT.category
order by TT.id

having min(start) > now() ensures that all of the slot dates are greater than now (within training_session_id, type_id group).

count(D.type_id) counts only not null values, so when there is no match from the left join the D.type_id value will be null and the count(D.type_id) will be 0.

See demo.