SQLite Forum

LEFT JOIN Misunderstanding
Login
You need to move the join conditions from the <code>WHERE</code> clause to <code>ON</code> clauses in each <code>JOIN</code>. Having them in the <code>WHERE</code> clause is equivalent only for inner joins.

```
    WITH LastEntries (ProjID, pl_insert, pe_insert)
    AS
    (
      SELECT projid,
             max(InsertDate),
             (SELECT max(insertdate) FROM project_extras where projid = e.projid)
        FROM project_list e
        GROUP BY projid
     )
    SELECT a.ProjID, a.Project_Name, count(c.CreatedDate), a.Manager
    FROM Project_List AS a
    LEFT JOIN Project_Extras AS b
        ON a.ProjID = b.ProjID
    LEFT JOIN Project_Highlights AS c
        ON a.ProjID IS c.ProjID
    LEFT JOIN LastEntries AS d
        ON a.ProjID = d.projid
    WHERE
        c.CreatedDate LIKE '2021-07-%' 
    AND
        b.MonthlyRpt = 'y'
    AND
        b.BudgetYear = '2021'
    AND
        a.InsertDate = d.pl_insert
    AND
        b.InsertDate = d.pe_insert
    GROUP BY a.ProjID
    ORDER BY a.Manager, a.ProjID
    ;
```