LEFT JOIN after Group By and HAVING
<p>I have a view cnst_prsn_nm . I want to check for records which share same cnst_mstr_id and same last name but differ on first names. So I did in Teradata SQL</p>

<pre><code>SELECT  TOP 20 prsn_nm_a.cnst_mstr_id  FROM arc_mdm_vws.bz_cnst_prsn_nm prsn_nm_a
INNER JOIN arc_mdm_vws.bz_cnst_prsn_nm prsn_nm_b
    ON prsn_nm_a.cnst_mstr_id = prsn_nm_b.cnst_mstr_id
GROUP BY prsn_nm_a.cnst_mstr_id
HAVING COUNT(DISTINCT prsn_nm_a.bz_cnst_prsn_last_nm) = 1
WHERE prsn_nm_a.bz_cnst_prsn_first_nm &lt;&gt; prsn_nm_b.bz_cnst_prsn_first_nm
</code></pre>

<p>And then for those records' cnst_mstr_ids, I want to check another table cnst_mstr . 
Basically I want to check for where <strong>left join IS NULL</strong> </p>

<pre><code>LEFT JOIN arc_mdm_vws.bzal_cnst_mstr mstr_new
    ON prsn_nm_a.cnst_mstr_id = mstr_new.new_cnst_mstr_id
WHERE mstr_new.new_cnst_mstr_id IS NULL
</code></pre>

<p>So my query becomes essentially </p>

<pre><code>SELECT  TOP 20 prsn_nm_a.cnst_mstr_id  FROM arc_mdm_vws.bz_cnst_prsn_nm prsn_nm_a
INNER JOIN arc_mdm_vws.bz_cnst_prsn_nm prsn_nm_b
    ON prsn_nm_a.cnst_mstr_id = prsn_nm_b.cnst_mstr_id
GROUP BY prsn_nm_a.cnst_mstr_id
HAVING COUNT(DISTINCT prsn_nm_a.bz_cnst_prsn_last_nm) = 1
WHERE prsn_nm_a.bz_cnst_prsn_first_nm &lt;&gt; prsn_nm_b.bz_cnst_prsn_first_nm
LEFT JOIN arc_mdm_vws.bzal_cnst_mstr mstr_new
    ON prsn_nm_a.cnst_mstr_id = mstr_new.new_cnst_mstr_id
WHERE mstr_new.new_cnst_mstr_id IS NULL
</code></pre>

<p>But there are two WHERE Clauses . Also LEFT JOIN cannot be there directly after HAVING . How can I have a left join after Group By and HAVING Clause when there is Filter associated with the Grouping ?</p>