MYSQL joining 3 tables with left join

Question

I have these tables:

pupils: (id_pupil, name, surname, email, user, pass, level, class)
incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester)
qualifications: (id_qualification, qualification, date, time, subject, id_pupil, id_user, id_trimester, type_qualification)

and i did this query:

SELECT 
    pupils.id_pupil
  , name
  , surname
  , round(avg(qualifications.calificacion),2) as average
  , count(qualifications.calificacion) as count
  , COALESCE(sum(type_incident='miss' and level=1 and class='A' and id_trimester=1),0) as misses
  , COALESCE(sum(type_incident='delay' and level=1 and class='A' and id_trimester=1),0) as delays
  , COALESCE(sum(type_incident='attitude' and level=1 and class='A' and id_trimester=1),0) as attitude
  , COALESCE(sum(type_incident='miss_justif' and level=1 and class='A' and id_trimester=1),0) as misses_justificadas 
FROM 
    pupils 
    left join incidents         ON incidents.id_pupil=pupils.id_pupil
    left join qualifications    ON qualifications.id_pupil=pupils.id_pupil
WHERE 
    level=1
    and class='A' 
    and id_trimester=1 
    and type_qualification='class' 
    and qualifications.id_trimester=incidents.id_trimester
GROUP BY id_pupil

But the problem is, the correct result i would have to get it would be:

And i get:

The problem is that multiplicate the number of class qualifications and the misses (17x6(2 misses, 1 delay, 1 attitude and 2 misses_just)=102)) and i don´t want that. I want the correct information showed in the first picture.

Would you like to help me, please?

Thanks!

Answer 1

I suggest you first read this question and answer Help with this query.

Then try to rewrite your queries using Option 3 (or all of the options). The following is a first attempt at option 1:

SELECT 
    p.id_pupil
  , p.name
  , p.surname
  , ROUND(AVG(q.calificacion),2) AS average
  , COUNT(DISTINCT q.id_qualification) AS count
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss'        THEN i.id_incidents END)
       AS misses
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'delay'       THEN i.id_incidents END)
       AS delays
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'attitude'    THEN i.id_incidents END)
       AS attitude
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss_justif' THEN i.id_incidents END)
       AS misses_justificadas
FROM 
    pupils                   AS p 
    LEFT JOIN incidents      AS i  ON  i.id_pupil = p.id_pupil
                                   AND i.id_trimester = 1
    LEFT JOIN qualifications AS q  ON  q.id_pupil = p.id_pupil
                                   AND q.id_trimester = 1
                                   AND q.type_qualification = 'class' 
WHERE 
    p.level = 1
    AND p.class = 'A' 
    AND p.id_trimester = 1 
GROUP BY 
    p.id_pupil 
  , p.name
  , p.surname ;

Answer 2

Quick and dirty fix:

SELECT 
    pupils.id_pupil
  , name
  , surname
  , round(avg(qualifications.calificacion),2) as average
  , (select count(calificacion) from qualifications where id_pupil = pupils.id_pupil and qualifications.id_trimester=incidents.id_trimester and type_qualification='class') as count
  , COALESCE(sum(type_incident='miss' and level=1 and class='A' and id_trimester=1),0) as misses
  , COALESCE(sum(type_incident='delay' and level=1 and class='A' and id_trimester=1),0) as delays
  , COALESCE(sum(type_incident='attitude' and level=1 and class='A' and id_trimester=1),0) as attitude
  , COALESCE(sum(type_incident='miss_justif' and level=1 and class='A' and id_trimester=1),0) as misses_justificadas 
FROM 
    pupils 
    left join incidents         ON incidents.id_pupil=pupils.id_pupil
WHERE 
    level=1
    and class='A' 
    and id_trimester=1 
GROUP BY id_pupil