title: Multiple Sqlite3 Joins with Window Function
body: <p>I have three tables I want to join.</p>
  <ol>
  <li>Main client table.  I also want to exclude &quot;TERM&quot; records based on FLAG column.</li>
  <li>BSP table - Match on Client Table, with most recent by date.</li>
  <li>HRC table - Match on Client Table, with most recent by date.</li>
  </ol>
  <p>This should have all non-term clients with the date of most recent BSP and most recent HRC in their own columns.</p>
  <p>I am unable to get the third table joined properly.</p>
  <p>What I've tried:</p>
  <pre><code>SELECT *
  
  from 
  
  clients 
  left join
  ( 
    select 
      Hipaa_Short b_h, BSP_Date, 
      row_number() over (PARTITION by Hipaa_Short 
                       ORDER by BSP_Date DESC) 
    as  rn_b
    from BSP
  ) B
  
  on Clients.Hipaa_Short = B.b_h
  where Clients.Status is not &quot;TERM&quot;
  
  
  ;
  
  </code></pre>
  <p><strong>EDIT</strong></p>
  <p>Changed to this, but not able to only have 1st record from BSP and HRC:</p>
  <pre><code>
  SELECT *
  
  from 
  
  clients 
  left join
  ( 
    select 
      Hipaa_Short b_h, BSP_Date, 
      row_number() over (PARTITION by Hipaa_Short 
                       ORDER by BSP_Date DESC) 
    as  rn_b
    from BSP
  ) B
  
  on Clients.Hipaa_Short = B.b_h
  
  left join
  ( 
    select 
      Hipaa_Short h_h, HRC_Date, 
      row_number() over (PARTITION by Hipaa_Short 
                       ORDER by HRC_Date DESC) 
    as  rn_h
    from HRC
  ) H
  
  on Clients.Hipaa_Short = H.h_h
  
  where Clients.Status is not &quot;TERM&quot;
  ;
  </code></pre>
  <p>Table Setup</p>
  <pre><code>CREATE TABLE Clients (
    &quot;id_pk&quot;   INTEGER,
    Hipaa_Short TEXT,
    Status TEXT, 
    Start_Date TEXT, 
    PRIMARY KEY(&quot;id_pk&quot;)
  );
  
  INSERT INTO Clients (Hipaa_Short, Status, Start_Date) VALUES 
  (&quot;Aaliyah&quot;, &quot;&quot;          ,&quot;2021-02-01&quot;)
  ,(&quot;Aiden&quot;,  &quot;&quot;          ,&quot;2020-12-01&quot;)
  ,(&quot;Amelia&quot;,     &quot;TERM&quot;  ,&quot;2021-08-01&quot;)
  ,(&quot;Aria&quot;,   &quot;&quot;          ,&quot;2021-07-01&quot;)
  ,(&quot;Ava&quot;,    &quot;&quot;          ,&quot;2021-06-01&quot;)
  
  ,(&quot;Avery&quot;,  &quot;&quot;          ,&quot;2021-03-01&quot;)
  ,(&quot;Benjamin&quot;,&quot;&quot;         ,&quot;2021-10-01&quot;)
  ,(&quot;Bob&quot;,&quot;&quot;              ,&quot;2021-02-01&quot;)
  ,(&quot;Caden&quot;,  &quot;&quot;          ,&quot;2021-02-01&quot;)
  ,(&quot;Camilla&quot;,    &quot;&quot;      ,&quot;2021-08-01&quot;)
  
  ,(&quot;Carter&quot;, &quot;TERM&quot;      ,&quot;2021-01-01&quot;)
  ,(&quot;Charlotte&quot;,&quot;&quot;        ,&quot;2021-07-01&quot;)
  ,(&quot;Eliana&quot;, &quot;&quot;          ,&quot;2021-05-01&quot;)
  ,(&quot;Elijah&quot;, &quot;&quot;          ,&quot;2021-10-01&quot;)
  ,(&quot;Emma&quot;,   &quot;&quot;          ,&quot;2021-02-01&quot;)
  
  ,(&quot;Ethan&quot;,  &quot;&quot;          ,&quot;2020-12-01&quot;)
  ,(&quot;Evelyn&quot;, &quot;&quot;          ,&quot;2021-03-01&quot;)
  ,(&quot;Everly&quot;, &quot;&quot;          ,&quot;2021-01-01&quot;)
  ,(&quot;Grayson&quot;,&quot;TERM&quot;      ,&quot;2021-08-01&quot;)
  ,(&quot;Isabella&quot;,&quot;&quot;         ,&quot;2021-07-01&quot;)
  
  ,(&quot;Jackson&quot;,&quot;&quot;          ,&quot;2021-05-01&quot;)
  ,(&quot;James&quot;,  &quot;&quot;          ,&quot;2021-07-01&quot;)
  ,(&quot;Jayden&quot;, &quot;&quot;          ,&quot;2021-07-01&quot;)
  ,(&quot;Layla&quot;,  &quot;&quot;          ,&quot;2021-01-01&quot;)
  ,(&quot;Liam&quot;,   &quot;&quot;          ,&quot;2021-01-01&quot;)
  
  ,(&quot;Logan&quot;,  &quot;&quot;          ,&quot;2020-03-01&quot;)
  ,(&quot;Lucas&quot;,  &quot;&quot;          ,&quot;2020-05-01&quot;)
  ,(&quot;Luna&quot;,   &quot;&quot;          ,&quot;2021-06-01&quot;)
  ,(&quot;Mason&quot;,  &quot;&quot;          ,&quot;2021-08-01&quot;)
  ,(&quot;Mateo&quot;,  &quot;&quot;          ,&quot;2021-05-01&quot;)
  
  ,(&quot;Mia&quot;,    &quot;&quot;          ,&quot;2020-11-01&quot;)
  ,(&quot;Michael&quot;,&quot;&quot;          ,&quot;2021-10-01&quot;)
  ,(&quot;Mila&quot;,   &quot;&quot;          ,&quot;2021-05-01&quot;)
  ,(&quot;Noah&quot;,   &quot;&quot;          ,&quot;2021-07-01&quot;)
  ,(&quot;Oliver&quot;, &quot;&quot;          ,&quot;2021-01-01&quot;)
  
  ,(&quot;Olivia&quot;, &quot;&quot;          ,&quot;2021-01-01&quot;)
  ,(&quot;Riley&quot;,  &quot;&quot;          ,&quot;2021-08-01&quot;)
  ,(&quot;Sebastian&quot;,&quot;&quot;        ,&quot;2021-02-01&quot;)
  ,(&quot;Sophia&quot;,&quot;&quot;           ,&quot;2021-02-01&quot;)
  ,(&quot;Zoe&quot;,    &quot;&quot;          ,&quot;2021-05-01&quot;)
  
  ;
  
  
  create table BSP (
    &quot;id_pk&quot;   INTEGER,
    Hipaa_Short TEXT,
    BSP_Date TEXT, 
    PRIMARY KEY(&quot;id_pk&quot;)
    );
    
   
   INSERT INTO BSP (Hipaa_Short, BSP_Date)
    VALUES
    
  (&quot;Ava&quot;          ,&quot;2019-11-30&quot;)
  ,(&quot;Eliana&quot;      ,&quot;2020-07-31&quot;)
  ,(&quot;Ethan&quot;       ,&quot;2020-08-28&quot;)
  ,(&quot;Grayson&quot;     ,&quot;2019-12-15&quot;)
  ,(&quot;Jackson&quot;     ,&quot;2019-10-20&quot;)
  ,(&quot;Jackson&quot;     ,&quot;2020-10-20&quot;)
  ,(&quot;Layla&quot;       ,&quot;2019-04-13&quot;)
  ,(&quot;Luna&quot;        ,&quot;2019-04-12&quot;)
  ,(&quot;Mateo&quot;       , &quot;2020-09-14&quot;);
    
  
  
  create table HRC (
     &quot;id_pk&quot;  INTEGER,
    Hipaa_Short TEXT,
    HRC_Date TEXT, 
    PRIMARY KEY(&quot;id_pk&quot;)
    );
  
  
  INSERT INTO HRC (Hipaa_Short, HRC_Date)
  VALUES
  
  (&quot;Ava&quot;      ,   &quot;2020-08-21&quot;)
  ,(&quot;Eliana&quot;  ,   &quot;2020-07-31&quot;)
  ,(&quot;Ethan&quot;   ,   &quot;2019-07-24&quot;)
  ,(&quot;Grayson&quot; ,   &quot;2020-02-07&quot;)
  ,(&quot;Jackson&quot; ,   &quot;2020-09-14&quot;)
  ,(&quot;Layla&quot;   ,   &quot;2019-11-30&quot;)
  ,(&quot;Mila&quot;    ,   &quot;2019-01-01&quot;);
  
  
  </code></pre>
  <p>Desired Output</p>
  <pre><code>
  | id_pk | Hipaa_Short | Status | Start_Date | b_h     | BSP_Date   | rn_b | HRC_Date |
  | ----- | ----------- | ------ | ---------- | ------- | ---------- | ---- | ---------- |
  | 1     | Aaliyah     |        | 2021-02-01 |         |            |      |          |
  | 2     | Aiden       |        | 2020-12-01 |         |            |      |          |
  | 4     | Aria        |        | 2021-07-01 |         |            |      |          |
  | 5     | Ava         |        | 2021-06-01 | Ava     | 2019-11-30 | 1    | 2020-08-21 |
  | 6     | Avery       |        | 2021-03-01 |         |            |      |          |
  | 7     | Benjamin    |        | 2021-10-01 |         |            |      |          |
  | 8     | Bob         |        | 2021-02-01 |         |            |      |          |
  | 9     | Caden       |        | 2021-02-01 |         |            |      |          |
  | 10    | Camilla     |        | 2021-08-01 |         |            |      |          |
  | 12    | Charlotte   |        | 2021-07-01 |         |            |      |          |
  | 13    | Eliana      |        | 2021-05-01 | Eliana  | 2020-07-31 | 1    | 2020-07-31 |
  | 14    | Elijah      |        | 2021-10-01 |         |            |      |          |
  | 15    | Emma        |        | 2021-02-01 |         |            |      |          |
  | 16    | Ethan       |        | 2020-12-01 | Ethan   | 2020-08-28 | 1    | 2019-07-24 |
  | 17    | Evelyn      |        | 2021-03-01 |         |            |      |          |
  | 18    | Everly      |        | 2021-01-01 |         |            |      |          |
  | 20    | Isabella    |        | 2021-07-01 |         |            |      |          |
  | 21    | Jackson     |        | 2021-05-01 | Jackson | 2020-10-20 | 1    |          |
  | 22    | James       |        | 2021-07-01 |         |            |      |          |
  | 23    | Jayden      |        | 2021-07-01 |         |            |      |          |
  | 24    | Layla       |        | 2021-01-01 | Layla   | 2019-04-13 | 1    | 2019-11-30 |
  | 25    | Liam        |        | 2021-01-01 |         |            |      |          |
  | 26    | Logan       |        | 2020-03-01 |         |            |      |          |
  | 27    | Lucas       |        | 2020-05-01 |         |            |      |          |
  | 28    | Luna        |        | 2021-06-01 | Luna    | 2019-04-12 | 1    |          |
  | 29    | Mason       |        | 2021-08-01 |         |            |      |          |
  | 30    | Mateo       |        | 2021-05-01 | Mateo   | 2020-09-14 | 1    |          |
  | 31    | Mia         |        | 2020-11-01 |         |            |      |          |
  | 32    | Michael     |        | 2021-10-01 |         |            |      |          |
  | 33    | Mila        |        | 2021-05-01 |         |            |      | 2019-01-01 |
  | 34    | Noah        |        | 2021-07-01 |         |            |      |          |
  | 35    | Oliver      |        | 2021-01-01 |         |            |      |          |
  | 36    | Olivia      |        | 2021-01-01 |         |            |      |          |
  | 37    | Riley       |        | 2021-08-01 |         |            |      |          |
  | 38    | Sebastian   |        | 2021-02-01 |         |            |      |          |
  | 39    | Sophia      |        | 2021-02-01 |         |            |      |          |
  | 40    | Zoe         |        | 2021-05-01 |         |            |      |          |
  
  
  </code></pre>
  <hr />
  <p><a href="https://www.db-fiddle.com/f/aQJv5HTj5vsApmPEUBR2n/4" rel="nofollow noreferrer">View on DB Fiddle</a></p>
  <pre><code>
  
  https://www.db-fiddle.com/f/aQJv5HTj5vsApmPEUBR2n/4
  
  </code></pre>