<p>I need to join two tables: `our_sample` and `tls207_pers_appln`, which come from PATSTAT. </p> <p>`our_sample` has 4 columns: appln_id, appln_auth, appln_nr, appln_kind. </p> <p>`tls207_pers_appln` has 4 columns: appln_id, person_id, applt_seq_nr, invt_seq_nr. </p> <p>`our_sample` has 2191 rows, some of which (60 rows) have no corresponding data in `tls207_pers_appln`. </p> <p>Because I wanted to keep all appln_ids in `our_sample` (even if they have no matching information in `tls207_pers_appln`), I used a RIGHT JOIN to join the two tables. </p> <p>However, the generated view `t2_tot_in_patent` has only 2096 appln_ids. </p> <p>This is partly due to the restrictions I set (35 patents were discarded because I only selected those with `HAVING MAX(invt_seq_nr) > 0`, which is acceptable). But this should result in 2191-35 = 2156 patents. </p> <p>Instead, I got 2096 patents, namely: 2191 (in our_sample) - 60 (missing appln_id in our_sample) - 35 (appln_id with invt_seq_nr = 0). </p> <p>But the whole purpose of using RIGHT JOIN is that these 60 patents should not be lost. Why is this so? </p> <pre class="brush:php;toolbar:false;">--Compile the total number of inventors per patent: t2_tot_in_patent DROP VIEW IF EXISTS t2_tot_in_patent; CREATE VIEW t2_tot_in_patent AS SELECT m.appln_id, MAX(invt_seq_nr) AS tot_in_patent FROM patstat2022a.tls207_pers_appln AS t7 RIGHT OUTER JOIN cecilia.our_sample AS m ON t7.appln_id = m.appln_id GROUP BY appln_id HAVING MAX(invt_seq_nr) > 0</pre> <p><br /></p>

GROUP BY m.appln_id

HAVING MAX(invt_seq_nr) > 0 OR MAX(invt_seq_nr) IS NULL