mysql select statement for multiple table
<p><a href="https://i.stack.imgur.com/k1PoA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/k1PoA.png" alt="enter image description here"></a></p>

<p>This is just a test project. I want to know how to <strong>select All professors with more than 5 failed students in a subject</strong> </p>

<p>I already know how to select <strong>All professors with at least 2 subjects</strong> with the following query: </p>

<pre><code>SELECT    paulin_professors.*,
          IFNULL(sub_p.total, 0) num
FROM      paulin_professors
LEFT JOIN ( SELECT   COUNT(*) total, pau_profid
            FROM     paulin_profsubject
            GROUP BY pau_profid
          ) sub_p ON (sub_p.pau_profid = paulin_professors.pau_profid)
    WHERE sub_p.total &gt;= 2;
</code></pre>

<p>I know I'm close but I can't get it to work (All professors with more than 5 failed students in a subject) . Any ideas? TIA</p>